[dba-SQLServer] Storing sql in database and other stuff

John,

I did something similar...I need to run over 100 queries, in order...and did not want to have to do it individually.  A number of people fussed about 'dynamic' SQl statements...but what I did was store the 'where clause' of my statements in a table...Pulled them in with query analyzer using a cursor...looped through each one and passed it to a stored procedure that built the rest of my SQl statement...then executed it.

Saved a lot of time this way.  I'd be interested in any faster solutions.

I used the char(34) in the SP...because I had single quotes in some of my criteria.

Hope it helps,

Mark A. Matte


********CALL SP**********
declare @ID nvarchar(255)
declare MyCursor cursor
for select CriteriaSQL from tblcriteria where requestname = 'MyName'
open mycursor
fetch next from mycursor
into @ID

while (@@fetch_status =0)

Begin
	exec Select_Records_1 @ID
	fetch next from mycursor
	into @ID
End
Close mycursor
deallocate mycursor

********End SP**********

********Start SP**********
CREATE PROCEDURE [Select_Records_1] @ID nvarchar(255)  AS
declare @sql as varchar(1000)
declare @ID2 as nvarchar(255)
declare @nsql as nchar(3000)
select @ID2=replace(replace(@ID,nchar(34),"x"),nchar(37),"x")
set @sql='insert into tblFrontWash_ID(case_id, Criteria) SELECT case_id, ' + char(34) + at ID2 + char(34) + ' from tblCase where creation_date between ' + char(34) + '1/1/2006'  + char(34) +' and '  + char(34) +'11/1/2007' + char(34) +' and ' + at ID
set @nsql=@sql
set nocount on
exec sp_executesql @nsql
set nocount off
********End SP**********


> From: jwcolby at colbyconsulting.com
> To: dba-sqlserver at databaseadvisors.com
> Date: Mon, 24 Mar 2008 16:21:54 -0400
> Subject: [dba-SQLServer] Storing sql in database and other stuff
>
> Guys, here is the deal.
>
> I have a sequence of steps which need to be performed in an rather exact
> order to get an order filled. These steps include running a bunch of action
> queries in a specific order. At the moment I have built the queries and
> have them stored in a directory but I KNOW that somehow I actually saved the
> queries inside of my database one time. I think maybe I saved them as
> stored procedures though I am not sure.
>
> So, I need to figure out how to save the queries inside of my database, then
> I need to build a stored procedure to run all of these queries (or stored
> procedures?) in order. If I can accomplish that then building an order will
> drop to an hour instead many hours.
>
> So, how do I store the queries inside of a database?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_________________________________________________________________
Test your Star IQ
http://club.live.com/red_carpet_reveal.aspx?icid=redcarpet_HMTAGMAR