Dynamic SQL and COALESCE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL and COALESCE

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2003-11-06 : 16:01:43

I have the following snipped of T-SQL that I can't get to compile.

SET @SQL = N'SELECT COUNT(*) AS RecordCount'
SET @SQL = @SQL + N' FROM MediaInfo'
SET @SQL = @SQL + N' WHERE AccountNo = ' + CAST(@AccountNo AS NVARCHAR(10))
SET @SQL = @SQL + N' AND ' + CAST(@DateType AS NVARCHAR(25)) + ' BETWEEN ''' + CAST(@BeginDate AS NVARCHAR(20)) + ''' AND ''' + CAST(@EndDate AS NVARCHAR(20)) + ''''
SET @SQL = @SQL + N' AND ' + COALESCE(@DataSetName, 'DataSetName')

What is blowing up is when I pass in a value to the @DataSetName parameter. Because everything is escaped the value in the parameter is being treated as what was entered and not quoted. I've tried modifying the value stored in the parameter by wrapping it in CHAR(39)'s but that didn't do anything.

Can anyone help me resolve this? I'm beating my head against the table.

FYI: Everything above the COALESCE line works, I'm trying to modify the procedure to accept additional parameters that may or may not be provided (stored proc is re-used in many situations).

Thanks in advance!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 16:09:33

Why are you using Dynamic SQL?

Did you try to just print out, not exucute, the string @SQL to see what it looks like?

What is that last condition supposed to be?

SET @SQL = @SQL + N' AND ' + COALESCE(@DataSetName, 'DataSetName')

there's no operator or comparison being doone, so your final string is something like:

WHERE Date between d1 and d2 AND something

which is not a valid condition.

- Jeff

KHeon
Posting Yak Master

135 Posts

Posted - 2003-11-06 : 16:23:47

Sorry about that. It should read:

SET @SQL = N'SELECT COUNT(*) AS RecordCount'
  SET @SQL = @SQL + N' FROM MediaInfo'
  SET @SQL = @SQL + N' WHERE AccountNo = ' + CAST(@AccountNo AS NVARCHAR(10))
  SET @SQL = @SQL + N' AND ' + CAST(@DateType AS NVARCHAR(25)) + ' BETWEEN ''' + CAST(@BeginDate AS NVARCHAR(20)) + ''' AND ''' + CAST(@EndDate AS NVARCHAR(20)) + ''''
  SET @SQL = @SQL + N' AND DataSetName = ' + COALESCE(@DataSetName, 'DataSetName')

I'm using dynamic sql to handle paging and custom sorting of the data. This system was optimized for millions of records, and at times many hundreds of thousands could be returned (clients request, not our recommendation). I did some trickery to minimize the number of records that get pulled into the temp table by determining which half of the dataset you are actually on and handling it accordingly. I'd love to revisit this but for the time being it's not in the cards, only adding additional search paramaters.

Thanks for your reply.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 16:53:10

you have no quotes around the dataset name.

Always print out the dynamic SQL you are generating and look at it by hand when you get errors. During the development process, ALWAYS just print the strings and analyze them by hand. If you can't print it out and cut and paste it into Query analyzer, then it won't run within a stored proc either.

- Jeff

KHeon
Posting Yak Master

135 Posts

Posted - 2003-11-07 : 07:58:18

Thanks. I've tried to do a PRINT @SQL, even tried converting it to VARCHAR and nothing prints out. I'll move forward with your suggestion though. Thanks!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-07 : 10:39:30

What's with all of the unicode? esp. for dynamic sql...

my guess is that one of your variables is null...evaluating the whole string to nulll...probably why you don't "see" anything



DECLARE   @SQL varchar(8000), @AccountNo int, @DateType varchar(25)
	, @BeginDate datetime, @EndDate datetime, @DataSetName varchar(255)

SELECT @AccountNo = 1234567890, @DateType = 'Col1', @BeginDate = '01/01/03', @EndDate = '12/31/03'
	, @DataSetName = 'BXBB72.X002548.TEST' 

SET @SQL = 'SELECT COUNT(*) AS RecordCount'
	+  ' FROM MediaInfo'
  	+  ' WHERE AccountNo = '
	+  CAST(@AccountNo AS VARCHAR(10))
	+  ' AND ' + CAST(@DateType AS VARCHAR(25)) 
	+  ' BETWEEN ''' + CAST(@BeginDate AS VARCHAR(20)) + ''' AND '''
	+  CAST(@EndDate AS VARCHAR(20)) + ''''
	+  ' AND DataSetName = ' + '''' 
	+ COALESCE(@DataSetName, 'DataSetName') + ''''

SELECT @SQL

SELECT @AccountNo = NULL, @DateType = 'Col1', @BeginDate = '01/01/03', @EndDate = '12/31/03'
	, @DataSetName = 'BXBB72.X002548.TEST' 

SET @SQL = 'SELECT COUNT(*) AS RecordCount'
	+  ' FROM MediaInfo'
  	+  ' WHERE AccountNo = '
	+  CAST(@AccountNo AS VARCHAR(10))
	+  ' AND ' + CAST(@DateType AS VARCHAR(25)) 
	+  ' BETWEEN ''' + CAST(@BeginDate AS VARCHAR(20)) + ''' AND '''
	+  CAST(@EndDate AS VARCHAR(20)) + ''''
	+  ' AND DataSetName = ' + '''' 
	+ COALESCE(@DataSetName, 'DataSetName') + ''''

SELECT @SQL

But go with what Jeff said...

Brett

8-)

KHeon
Posting Yak Master

135 Posts

Posted - 2003-11-07 : 11:03:53

Thanks all. I'm using unicode because I call sp_executesql which accepts a unicode string, thus the NVARCHAR.

I have solved this by writting in a series of IF checks to see if the parameters are NOT NULL and including them without using a COALESCE function.

What I think was happening to invalidate my return is pretty much what X002548 said, a NULL seems to be screwing with my results.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources