Placeholders in EXEC statements

Question

Placeholders in EXEC statements

Mark Green

SSCrazy

Points: 2085
More actions
March 7, 2007 at 10:39 am

#72636

Does MS SQL Server support placeholders being used in EXEC statements? I have a Perl DBI application (that fires EXEC statements aginst my reception SPROC) that I want to use placehoders with. I am having difficulty with it as it gives me the error message:
Syntax error converting the nvarchar value 'x' to a column of data type int.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

because you are doning dynamic SQL, you'll want to put the SQL command in a string with a placeholder, and then replace the placeholder prior to the real exec statement. something like this:

declare

@isql varchar(2000),

@dbname varchar(64)

SELECT @dbname =somedb from sometable

select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

select @isql = replace(@isql,'@dbname',@dbname)

print

@isql

exec(@isql

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Green SSCrazy Points: 2085 More actions · Answer 2

Sounds interesting. But I'm not sure I understand what you are showing me. I suppose the questions that I originally posted should have been: Firstly, does MS SQL Server support placeholders of the kind that Perl DBI use. Secondly, if so, do they apply to EXEC statements? If the answer to both of these is no. What alternative solutions could be applied at the T-SQL level?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

I'm not totally sure what you're asking ( never used Perl ) but you might want to check out sp_executesql in BOL.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/