Solved

How do i add parameters in dynamic sql

Posted on 2010-08-27
9
304 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 

Author Comment

by:Mr_Shaw
Comment Utility
This script is taken from a larger script where I have to use dynamic SQL.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Accepted Solution

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

Author Comment

by:Mr_Shaw
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now