Mr_Shaw
asked on
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?
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?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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)
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_CodeASKER
This script is taken from a larger script where I have to use dynamic SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Open in new window