dynamic query in stored procedure

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 query in stored procedure

Author

Topic

vishalg
Starting Member

29 Posts

Posted - 2009-06-24 : 00:06:02

Hello,

I have to execute a dynamic query inside a stored procedure. I tried using EXEC(@SQL) inside a stored procedure. It is giving me some problems.
I am giving example of the problem below.


create procedure [dbo].[testing]
(
 @PATIENTID VARCHAR(50)
)
as

DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

SET @SQL='SELECT * FROM patienttable where patientid=@PATIENTID
IF(some condition)
BEGIN
@SQL=@SQL+' AND patientlocation=@LOCATION'
END

IF(some condition)
BEGIN
@SQL=' AND patientethinicity=@ETHNICITY'
END

/* after a few more if conditions, now finally */

IF(some condition)
BEGIN
EXEC(@SQL)
ELSE

RETURN
GO
EXEC dbo.testing '1'

When i try to execute the above.. I have the following error.

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@PATIENTID".

-- Please help. THANKS

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-24 : 00:20:12

[code]
alter procedure [dbo].[testing]
(
@PATIENTID VARCHAR(50)
)
as

DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

SET @SQL='SELECT * FROM patienttable where patientid='+@PATIENTID
IF(some condition)
BEGIN
@SQL=@SQL+' AND patientlocation='+@LOCATION
END

IF(some condition)
BEGIN
@SQL=' AND patientethinicity='+@ETHNICITY
END

/* after a few more if conditions, now finally */

IF(some condition)
BEGIN
EXEC(@SQL)
ELSE

RETURN
GO
EXEC dbo.testing '1'
[/code]

vishalg
Starting Member

29 Posts

Posted - 2009-06-24 : 01:15:24

THANKS A LOT.. IT WORKS.. but now m caught in some new error.
Will post if cannot solve it. But thank you :)

Also, I have one fundamental question about stored procedures and select statements. I have a stored procedure which i basically use for matching information in the database. Thus, in the stored proc I do a lot of select queries on a "view" i have created. Also, this stored procedure is goin to run over half a million of records.

So my question is, will the select queries hamper my server's performance too bad??

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-24 : 05:15:31

Before you go any further read this:

http://www.sommarskog.se/dynamic_sql.html

Appending variables to a dynamic string the way that bklr suggested leaves yourself wide open to sql injection (unless you check your paramaters carefully before passing them to this stored proc).

Also -- if the code you posted is actually what you are going to use then there is absolutely no need to use dynamic sql here at all. With no dynamic sql there is no risk of injection.

Your code can be rewritten like this:


create procedure [dbo].[testing]
(
 @PATIENTID VARCHAR(50)
)
AS

DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

DECLARE @condition1 BIT                SET @condition1 = 0
DECLARE @condition2 BIT                SET @condition2 = 0

IF (some condition) SET @condition1 = 1
IF (some condition) SET @condition2 = 1

IF (some condition) SELECT
	*
FROM
	patienttable
WHERE
	patientID = @PATIENTID

	AND (
		patientlocation=@LOCATION
		OR
		@condition1 = 0
	)

	AND (
		patientethinicity=@ETHNICITY
		OR
		@condition2 = 0
	)
ELSE RETURN
GO

EXEC dbo.testing '1'

You haven't posted any code in your stored proc where you actually assign any values to @LOCATION & @ETHNICITY so I assume what you posted isn't the whole story.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vishalg
Starting Member

29 Posts

Posted - 2009-06-29 : 03:04:31

the solution u gave surely helps but in my case it does not.

i did exactly as u suggested..


DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

SET @SQL='SELECT * FROM patienttable where patientid='+@PATIENTID
IF(some condition)
BEGIN
@SQL=@SQL+' AND patientlocation='+@LOCATION
END

IF(some condition)
BEGIN
@SQL=' AND patientethinicity='+@ETHNICITY
END

i tried printing the @sql string and the output was

SELECT * FROM patienttable where patientid=1 AND patientlocation=New York AND patientethinicity=asian

but the actual query should be

SELECT * FROM patienttable where patientid='1' AND patientlocation='New York' AND patientethinicity='asian'

so how do i do this??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-29 : 03:17:09

[code]
DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

SET @SQL='SELECT * FROM patienttable where patientid='+''''+@PATIENTID+''''
IF(some condition)
BEGIN
@SQL=@SQL+' AND patientlocation='+''''+@LOCATION+''''
END

IF(some condition)
BEGIN
@SQL=' AND patientethinicity='+''''+@ETHNICITY+''''
END
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:20:42

DECLARE @LOCATION VARCHAR(50)
DECLARE @ETHNICITY VARCHAR(50)
DECLARE @SQL VARCHAR(MAX)

SET @SQL='SELECT * FROM patienttable where patientid= ' + quotename(@PATIENTID, '''')
IF(some condition)
BEGIN
@SQL=@SQL+' AND patientlocation='+quotename(@LOCATION, '''')
END

IF(some condition)
BEGIN
@SQL=' AND patientethinicity='+quotename(@ETHNICITY, '''')
END

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

vishalg
Starting Member

29 Posts

Posted - 2009-06-29 : 03:32:13

hey thanks a lot for the quick and correct reply.

got it running. thanks a lot :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 03:50:14

I hope you went for the QUOTENAME approach.

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

vishalg
Starting Member

29 Posts

Posted - 2009-06-29 : 04:03:08

m sorry.. the multiple quotes method looked easier..
but does it matter? are they both too different?
i am a newbie.. would surely like to know..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 04:14:24

Have a fixed number of quotes makes your code still vulnerable to SQL injection.
bklr should know this, after more than 1000 posts.

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-29 : 04:18:30

I'll say it again!

If you *care at all* about getting better at this then READ THIS:

http://www.sommarskog.se/dynamic_sql.html

Every question you asked would have been explained if you had only read that article.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources