Dynamic param substition - ideas?/suggestions? TOUGH ONE I THINK. -- sql server programming

sql server programming : Dynamic param substition - ideas?/suggestions? TOUGH ONE I THINK.

jobs
2/16/2007 8:03:40 PM

Hello.

I have a application that will accept parameters from users. I'd like
to give them a way to build dynamic parameters that substitute strings
with formated dates allowing them to define Parms that are date smart.

Say they define some sql that results in the desired date and
format..

@FOQ ='DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)' or something like
this with the right cast convert to result in 200701

Say they define a Parm format delimiting any sub vars with ** and
expecting **FOQ** to be substituted with First of Quarter in YYYYMM
format.

@Parmin = '/Quarterly/filea**FOQ**.txt'

and we nt @Parmout to result in something like this:

'/Quarterly/filea200701.txt'

so the sp, will first strip out **whaterver**, and use it to produce
it's result and then output it after substituting...

I'm a sql server noob.. what are my options?

sp_executesql ?
some form of regex? to find and replace **whatever**

A big thanks for any help or information!

KenJ
2/16/2007 8:42:12 PM

I would really lean away from creating SQL in the client and passing
it in for the server to execute. If the user set @FOQ = 'SHUTDOWN
WITH NOWAIT' you'd have a bit of a problem (I know, an extreme, rather
unlikely, example).

Before you consider the sp_executeSQL procedure, I would recommend
reading Erland Sommarskog's essay on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

You could definitely replace a delimited sub-string. However, it may
make more sense to pass in a format parameter then a CASE statement
that generates the proper date and format based on that:

SET @dateString = CASE @Format
WHEN 1 THEN DATEADD(qq, DATEDIFF(qq,
0,getdate()), 0)
WHEN 2 THEN current date
etc...

Have fun,

Ken

[quoted text, click to view]

jobs
2/17/2007 5:22:47 AM

Thanks for response - I'll definitely will be careful with dynamic
sql.. I read the article, but I must be missing something on how I can
connect the dots.

Say i want the results from a dynamic SQL to go to a variable in my
current session?

Apparently i have to declare variables inside the sql string. Any way
to redirect or pipe results of a dynamic sql into a variable? Any way
to include it as part of a select statement?

DECLARE @sql nvarchar(4000),
@gg nvarchar(1000)
SET @sql = 'declare @gg NVARCHAR(1000) SELECT @GG=5'
EXEC(@sql)
print @gg

Thank you!

KenJ
2/17/2007 8:57:30 AM

Since the execution of dynamic SQL happens in its own session, you'll
have to use sp_executesql with output parameter(s) to get values back
from your query. Here is your example using sp_executesql instead of
exec():

DECLARE @sql nvarchar(4000),
@gg nvarchar(1000)

--- we need to define our parameter for sp_executesql
DECLARE @parmString nvarchar(128)
SET @parmString = N'@GG nvarchar(1000) OUTPUT'

SET @sql = 'SELECT @GG=''5'''
EXEC sp_executesql @sql, @parmString, @gg = @gg OUTPUT
PRINT @gg

The dynamic sql article had an sp_executesql section that demonstrated
an output parameter here:
http://www.sommarskog.se/dynamic_sql.html#sp_executesql

Books-online has some more here:
http://msdn2.microsoft.com/en-us/library/ms188001.aspx

Ken

[quoted text, click to view]

Erland Sommarskog
2/17/2007 4:58:21 PM

jobs (jobs@webdos.com) writes:
[quoted text, click to view]

It appears that you need to read the article again. Particularly
the section on sp_executesql. That is what you use to get data back from
your dynamic SQL.

As for your original question, I think you are shooting over the
target. I think you can get a long way with predefining a bunch of
intervals in a table and then use these:

SELECT yadayada
FROM tbl
JOIN datenames d ON tbl.date BETWEEN d.startdate AND d.enddate
WHERE d.datename @somename

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn