How do i add parameters in dynamic sql

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?
Mr_ShawAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I presumed so. please read above comments
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Mr_ShawAuthor Commented:
This script is taken from a larger script where I have to use dynamic SQL.
0
 
Mr_ShawAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
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
 
Mr_ShawAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.