Pass param to Stored proc

Question

Post reply

Pass param to Stored proc

mtz676

SSCrazy Eights

Points: 9261
More actions
May 7, 2015 at 4:44 am

#303717

exec sp_executesql N'xp_cmdshell ''ping @id'''
How can I make the above code run ?
I want to pass @id parameter after selecting from a table column
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Using the @id variable to simulate your parameter, you can do something like this:

DECLARE @id NVarchar(15) = N'127.0.0.1'

DECLARE @strCmd Nvarchar(20) = N'ping ' + @id;

EXECUTE xp_cmdshell @strCmd;

Hopefully, this is a part of a DBA script or otherwise limited procedure. Of course, you'll want to size your parameters appropriately and do your normal checks on what you're going to execute.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

T-SQL is a horrible tool for this job and using the CPU of the SQL Server box to send pings is not a wise choice, generally speaking. Not to mention the security concerns that xp_cmdshell brings.

Are you sure you cannot code this in a different way or with a different tool?

-- Gianluca Sartori

Sean Lange SSC Guru Points: 286602 More actions · Answer 3

spaghettidba (5/7/2015)
T-SQL is a horrible tool for this job and using the CPU of the SQL Server box to send pings is not a wise choice, generally speaking. Not to mention the security concerns that xp_cmdshell brings.
Are you sure you cannot code this in a different way or with a different tool?

+10000

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Sean Lange (5/7/2015)
spaghettidba (5/7/2015)
T-SQL is a horrible tool for this job and using the CPU of the SQL Server box to send pings is not a wise choice, generally speaking. Not to mention the security concerns that xp_cmdshell brings.
Are you sure you cannot code this in a different way or with a different tool?
+10000

Heh, you raise a good point. It was early and I was focused on getting the problem solved and didn't consider the wisdom of doing so. Thanks to both of you for pointing it out. I learned from this post that I shouldn't post before coffee.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Pass param to Stored proc

Cookies on SQLServerCentral