Solved

How do i add parameters in dynamic sql

Posted on 2010-08-27
9
307 Views
Last Modified: 2012-05-10
I have the following

Create Procedure dbo.Test

(@PCT_Code varchar(10))

as

declare @sql varchar(max)

set @sql = 'select * from tbl1 where   (PctCodes.PCT_Code ='+ @PCT_Code+')'

exec (@sql)

This builds fine.

When I Execute the stored procedure I get the following error

exec [dbo].[Test]  'RWJ'

Invalid column name 'RT5'.

How do i add parameters in dynamic sql?
0
Comment
Question by:Mr_Shaw
  • 5
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33540118
why dynamic sql?
Create Procedure dbo.Test
(@PCT_Code varchar(10))
as
select * from tbl1 
where   PctCodes.PCT_Code = @PCT_Code

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33540126
and just in case you really, really needed dynamic sql:
Create Procedure dbo.Test
(@PCT_Code varchar(10))
as
declare @sql varchar(max)
set @sql = 'select * from tbl1 where  PctCodes.PCT_Code = '''+ replace(@PCT_Code,  '''', '''''') +''' )'

exec (@sql)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33540129
alternatively, you can make this "better":
Create Procedure dbo.Test
(@PCT_Code varchar(10))
as
declare @sql nvarchar(max)
set @sql = 'select * from tbl1 where   (PctCodes.PCT_Code = @p1 )'

exec sp_executesql @sql, N'@p1 varchar(10)', @PCT_Code

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Mr_Shaw
ID: 33540130
This script is taken from a larger script where I have to use dynamic SQL.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 425 total points
ID: 33540138
I presumed so. please read above comments
0
 

Author Comment

by:Mr_Shaw
ID: 33540164
I don't understand what you have written here

Create Procedure dbo.Test
(@PCT_Code varchar(10))
as
declare @sql nvarchar(max)
set @sql = 'select * from tbl1 where   (PctCodes.PCT_Code = @p1 )'

exec sp_executesql @sql, N'@p1 varchar(10)', @PCT_Code

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33540188
the sp_executesql can run sql with parameters ...
so the "inner sql" will "see" and use the parameter @p1 (second argument of the sp_executesql procedure, which defines the parameters for in and out), and the value for it follows as third argument ...

this example would be to have 2 parameters for the dynamic sql, 1 in, 1 out
Create Procedure dbo.Test
( @PCT_Code varchar(10)
, @result_col varchar(50) OUTPUT
)
as

declare @sql nvarchar(max)
set @sql = 'select @res = col2 from tbl1 where   (PctCodes.PCT_Code = @p1 )'

exec sp_executesql @sql, N'@p1 varchar(10), @res varchar(50) OUTPUT ', @PCT_Code , @result_col OUTPUT

Open in new window

0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 75 total points
ID: 33547645
Yes, I would definitely go with angelIIIs comment.  You should use sp_executesql for dynamic sql, because you can use parameters in the right way, not in the concatenating way.
0
 

Author Closing Comment

by:Mr_Shaw
ID: 33565591
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question