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

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 05:32:44

DECLARE @ctyid INT
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+@ctyid+''
EXEC (@SQL)

This is giving error how can i write this dynamic SQL.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 05:34:44

why do you need dynamic sql for this.
isnt this enough?


select * from dbo.TBL_ADDRESS
where adr_ID=@ctyid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 05:36:09

I assume your real scenario is something else. If you want to form dynamic string like this, it should be

SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+ CAST(@ctyid AS varchar(10))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 05:47:35

Yes True My Real Scenario is different.. i was just wondering why we can use it as INT as i changed it a bit and it worked fine for me.. but we need to convert it into VARCHAR why we cant use int directly???? is this because we are creating dynamic sql as nvarchar..??/

This is working but still i need to take parameter as varchar..? :-(

DECLARE @ctyid VARCHAR(100)
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+@ctyid+''
EXEC (@SQL)

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-02 : 05:50:21

The reason you can't do this is because SQL SERVER doesn't support automatic concatenation of strings and ints.

Your example is *wide open* to sql injection attack and is really dangerous.

Please read this page -- it will answer any and all questions you'd have about dynamic sql -- in particular when not to use it (which is most of the time).
http://www.sommarskog.se/dynamic_sql.html

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 05:55:15

Thnxx to both of you for the support. Thnxx very Much.. :-)

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 05:56:07

quote:
Originally posted by ashishashish

Yes True My Real Scenario is different.. i was just wondering why we can use it as INT as i changed it a bit and it worked fine for me.. but we need to convert it into VARCHAR why we cant use int directly???? is this because we are creating dynamic sql as nvarchar..??/

This is working but still i need to take parameter as varchar..? :-(

DECLARE @ctyid VARCHAR(100)
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+@ctyid+''
EXEC (@SQL)

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

even if its a int you've do casting in concatenate. so even varchar is fine
As Charlie pointed out this one is prone to SQLInjection attacks so make sure you understand consequences and also ways to avoid it before you implement it this way
I still can make out your exact scenario though which is prompting me to debate on the whole point of using D-SQL here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 06:11:43

why i need to use this is i am doing something in SQl in where clause like if search is based to 50+ parameters and i am writing logic for that thats y i like to use dynamic SQL. But i did it by using CASE statements itself. Why i asked this question is i need to understand the main cause why it is failing in this way. I understand the risks of SQL Injection and i likely to use SP_EXECUTESQL. I wrote this just for example as i need to understand the behaviour, and thanks to you as i am a novoice in SQl yet and try to learn it daily by own experiments.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-02 : 06:18:10

dynamic sql is a good choice for catch all type searches. The common alternative is stuff like


WHERE
   (e.[Surname] LIKE @p1 OR @p1 IS NULL)
   AND (e.[Firstname] LIKE @p2 OR @p2 IS NULL)
   AND (e.[StaffNumber] = @p3 IR @p3 IS NULL)
....
....

This kind of code leads to really bad plans. Dynamic sql here has no chance of sql injection and will generate a nice plan for each permutation..

Gail wrote a nice article on this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-02 : 06:19:22

ok fine.
so based on 50 + parameters you're going to build dynamic string and execute using sp_executesql?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 06:24:41

i was trying to do so, but i changed it in case kind of thing.
something like..

WHERE col1 = CASE WHEN @col1 IS NULL THEN Col1 else @col1 end...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 06:26:32

i also thought of writing it with the help of isnull... something like..

WHERE col1=ISNULL(@col1,col1)

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-02 : 06:30:51

Read
Gail wrote a nice article on this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 07:06:17

Thanks Charlie...this is really a good thing to know.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 07:43:57

" i likely to use SP_EXECUTESQL. I wrote this just for example as i need to understand the behaviour,"

With sp_ExecuteSQL you will not have to worry about the issue you are having with string concatenation and mixing up INT and CHAR.

SO: just move straight away to using sp_ExecuteSQL for this job, and that will also solve the SQL Injection risk too

You code:


DECLARE @ctyid INT
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+@ctyid+''
EXEC (@SQL)

will become:


DECLARE @ctyid INT
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID=@ctyid'

EXEC sp_ExecuteSQL @SQL, '@ctyid int', @ctyid

(Not 100% sure you can use NVARCHAR([re]MAX[/red]) as the parameter to sp_ExecuteSQL though, but the documentation will tell you that for sure ... or someone else here will know the answer to that)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-09-02 : 08:00:29

you can. In 2005 or better. Think OP's *actual* requirement though was how to best do multiple choice type filters.

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

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-02 : 08:22:03

I think the best way to do so is by using Dynamic SQL.. as i have read the post by
Gail Shaw
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

provided by you and i think if performance is the key than we must use Dynamic SQL method.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Kristen
Test

22859 Posts

Posted - 2011-09-02 : 08:52:57

quote:
Originally posted by Transact Charlie

Think OP's *actual* requirement though was how to best do multiple choice type filters.

Indeed

I just didn't want my example to "break", in case it was useful to the OP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-05 : 05:37:07

quote:
Originally posted by Kristen

" i likely to use SP_EXECUTESQL. I wrote this just for example as i need to understand the behaviour,"

With sp_ExecuteSQL you will not have to worry about the issue you are having with string concatenation and mixing up INT and CHAR.

SO: just move straight away to using sp_ExecuteSQL for this job, and that will also solve the SQL Injection risk too

You code:

DECLARE @ctyid INT
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID='+@ctyid+''
EXEC (@SQL)

will become:

DECLARE @ctyid INT
DECLARE @sql NVARCHAR(MAX)

SET @ctyid=1
SET @SQL='select * from dbo.TBL_ADDRESS
where adr_ID=@ctyid'

EXEC sp_ExecuteSQL @SQL, '@ctyid int', @ctyid

(Not 100% sure you can use NVARCHAR([re]MAX[/red]) as the parameter to sp_ExecuteSQL though, but the documentation will tell you that for sure ... or someone else here will know the answer to that)

EXEC sp_ExecuteSQL @SQL, '@ctyid int', @ctyid

should be

EXEC sp_ExecuteSQL @SQL, N'@ctyid int', @ctyid

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-09-05 : 05:48:23

Thanks Madhi, better than relying on implicit casting (assuming that that is even allowed ...)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources