passing parameter to stored procedure in bcp command

Question

Post reply

passing parameter to stored procedure in bcp command

saum70

SSC Eights!

Points: 993
More actions
October 30, 2019 at 11:50 am

#3693051

Hi,
I have to take output of stp using bcp command. the stp contains one parameter. I want to know how to pass parameter to stp and get the output. the batch contains below bcp command
set DBName=DBLive
set DBServerName=ServerLive
set FileName=ClosePrice_N.csv
bcp "EXEC "%DBName%..stp " " queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w
How to pass parameter to the stp
Kindly help.
Regards,
Saumik

Viewing 9 posts - 1 through 8 (of 8 total)

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

As always when you call a procedure, you put the parameter value after the stored procedure. For instance:

et DBName=DBLive

set DBServerName=ServerLive

set FileName=ClosePrice_N.csv

set param="My parameter value"

bcp "EXEC %DBName%..stp '%param%'" queryout %FileName% -Uadmin -Padmin -S%DBServerName% -t, -w

Note that you should only wrap %param% in single quotes if it is a string. You would not do this for a numeric parameter.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

saum70 SSC Eights! Points: 993 More actions · Answer 2

Hi Erland,

I followed the step as you guided. however the output did not come. On checking the profiler i found that parameter value is not getting passed. The value of parameter is time for e.g. 17:00:00 or 17:30:00

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

So how does your script actually look like?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

saum70 SSC Eights! Points: 993 More actions · Answer 4

saum70

SSC Eights!

Points: 993

October 30, 2019 at 1:26 pm

#3693099

20191030_185223

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

It would be better to paste the code for the script, using the insert/edit code sample button rather than posting a picture. That would also permit you hide your sa password.

Try

set param=17:30:00

without quotes, or else the quotes will be included in the value, which is not going to work well.

I hope that you realise that you are asking a question about how to use the DOS command line. While an SQL Server tool is the use case, it is not really an SQL Server question.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

saum70 SSC Eights! Points: 993 More actions · Answer 6

Noted about the type of the question as well as insert/edit code.

Now i passed the parameter without quotes, but still blank value is getting passed to the stp instead of the time.

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 7

I created this procedure in tempdb:

CREATE OR ALTER PROCEDURE slafs_sp @time time(0) AS
   SELECT * FROM sys.objects WHERE convert(time(0), modify_date) <= @time

set DBNAME=tempdb
set DBServerName=SOMMERWALD
set FileName=slask.csv
set param=23:59:59
bcp "EXEC %DBName%..slafs_sp '%param%'" queryout %FileName% -T -S%DBServerName% -t, -w

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

saum70 SSC Eights! Points: 993 More actions · Answer 8

Hi Erland,

Thanx for the help. i figured out the issue. there was space between Param variable and the value in Param definition.