Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do i add parameters in dynamic sql

Posted on 2010-08-27
9
Medium Priority
?
314 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

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 1700 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 300 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

Google Certified Professional - Cloud Architect

This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

704 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