Dynamic SQL problem - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL problem

Author

Topic

Tones
Starting Member

17 Posts

Posted - 2012-02-29 : 07:05:20

Ive seen this posted around this forum and others but havent been able to find a solution to my problem.

i have the following SP

ALTER PROCEDURE [dbo].[supervisorSearch] 
	-- Add the parameters for the stored procedure here

	

@statusIdInt Int,
@sectionIdInt Int,
@staff varchar(20)
	
As	

DECLARE @SQL nvarchar(1000)

SET @SQL = 
'SELECT tbl_Customers.surnameStr, tbl_Customers.ninoStr, tbl_Claim_Status.statusStr, tbl_Claims.targetDateDate, 
tbl_Claims.BfDateDate, tbl_Claims.claimIdInt, tbl_Customers.firstNameStr, tbl_Customers.dobDate FROM tbl_Customers 
INNER JOIN tbl_Claims ON tbl_Customers.customerIdInt = tbl_Claims.customerIdInt INNER JOIN tbl_Claim_Status 
ON tbl_Claims.claimStatusInt = tbl_Claim_Status.claimStatusIdInt 
WHERE tbl_Claims.claimStatusInt = @statusIdInt'

IF @sectionIDInt <> 0
SET @SQL = @SQL + ' And sectionIdInt = @sectionIdInt'

IF @staff <> 'ALL'
SET @SQL = @SQL + ' And allocatedToStr = @staff'

EXEC (@SQL)

I understand this wont work as the variables are outside of the dynamic sql statement. So i changed to this based on what i have found on here

ALTER PROCEDURE [dbo].[supervisorSearch] 
	-- Add the parameters for the stored procedure here

	

@statusIdInt Int,
@sectionIdInt Int,
@staff varchar(20)
	
As	

DECLARE @SQL nvarchar(1000)

SET @SQL = 
'SELECT tbl_Customers.surnameStr, tbl_Customers.ninoStr, tbl_Claim_Status.statusStr, tbl_Claims.targetDateDate, 
tbl_Claims.BfDateDate, tbl_Claims.claimIdInt, tbl_Customers.firstNameStr, tbl_Customers.dobDate FROM tbl_Customers 
INNER JOIN tbl_Claims ON tbl_Customers.customerIdInt = tbl_Claims.customerIdInt INNER JOIN tbl_Claim_Status 
ON tbl_Claims.claimStatusInt = tbl_Claim_Status.claimStatusIdInt 
WHERE tbl_Claims.claimStatusInt = '+@statusIdInt

IF @sectionIDInt <> 0
SET @SQL = @SQL + ' And sectionIdInt = '+@sectionIdInt

IF @staff <> 'ALL'
SET @SQL = @SQL + ' And allocatedToStr = '+@staff

EXEC (@SQL)

But this now brings up an error along the lines of "Conversion failed when converting the varchar value...(sql statement here)...to data type int.

My knowledge of SQL is pretty basic and this is my first attempt at a dynamic sql SP.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-29 : 07:08:29

Have a look at SP_EXECUTE_SQL stored procedure instead of EXEC.

N 56°04'39.26"
E 12°55'05.63"

Tones
Starting Member

17 Posts

Posted - 2012-02-29 : 08:43:20

Fixed it using the 2nd way listed above. And got round the Conversion error by using CAST(@StatusIdInt AS VARCHAR)

Though i dont know why that had to be done this way, but found the solution elsewhere and it works.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-29 : 08:56:19

Go read up on SQL injection (your code is vulnerable) and on how to use sp_executesql instead of EXEC.

Also
CAST(@StatusIdInt AS VARCHAR)

Do you know how long that varchar is if you don't specify a length?

--
Gail Shaw
SQL Server MVP

Tones
Starting Member

17 Posts

Posted - 2012-02-29 : 09:07:44

Thanks. Set the varchar size, and used sp_executesql and its working fine.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-29 : 12:36:40

Well, if you're using sp_executesql properly you won't need the cast to varchar. If you're still casting and concatenating you've missed the point of sp_executesql (parameterisation) and your code is still vulnerable to SQL injection.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources