Post reply

passing parameter to stored procedure in bcp command

  • 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

     

     

  • October 30, 2019 at 12:11 pm

    #3693060

    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]

  • October 30, 2019 at 1:02 pm

    #3693090

    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

  • October 30, 2019 at 1:08 pm

    #3693093

    So how does your script actually look like?

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

  • October 30, 2019 at 1:26 pm

    #3693099

    20191030_185223

  • October 30, 2019 at 1:36 pm

    #3693111

    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]

  • October 30, 2019 at 1:53 pm

    #3693115

    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.

  • October 30, 2019 at 4:54 pm

    #3693197

    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]

  • October 31, 2019 at 5:57 am

    #3693379

    Hi Erland,

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

     

     

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

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