Post reply

Passing more than one value to a parameter for a stored procedure

  • October 22, 2012 at 9:44 am

    #264538

    I know this question has been asked a thousand times, but I am starting out and I am struggling to get my head around it.

    I am trying to pass more than on Station ID to a parameter in a stored Procedure. Here is my code, could someone advise where I am going wrong. Am I on the right track, Once again I appreciate all help offered

    CREATE TYPE tabletype_StationLists AS TABLE (

    StationID varchar(40) NOT NULL PRIMARY KEY)

    DECLARE @Stations tabletype_StationLists

    INSERT INTO @Stations(StationID)

    Values ('DAP'),('CAP')

    DECLARE @StartDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 5)

    DECLARE @EndDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6)

    DECLARE @Type varchar(30)

    exec [up_FleetBalance_Control_Reporting_Services_Detailed] @Stations,Auto,@StartDate,@EndDate,7

  • October 22, 2012 at 9:53 am

    #1551480

    Hi Thomas, what problem are you running into?

    Thanks

    Gaz

  • October 22, 2012 at 9:59 am

    #1551488

    Tabled valued parameters are your friend here


    Clear Sky SQL
    My Blog[/url]

  • October 22, 2012 at 10:22 am

    #1551518

    Msg 2715, Level 16, State 3, Line 13

    Column, parameter, or variable #1: Cannot find data type tabletype_StationLists.

    Parameter or variable '@Stations' has an invalid data type.

    Msg 1087, Level 16, State 1, Line 6

    Must declare the table variable "@Stations".

    -This is as far as I can get.

  • October 22, 2012 at 10:29 am

    #1551525

    CREATE TYPE tabletype_StationLists AS TABLE (

    StationID varchar(40) NOT NULL PRIMARY KEY)

    go

    DECLARE @Stations tabletype_StationLists

    INSERT INTO @Stations(StationID)

    Values ('DAP'),('CAP')

    DECLARE @StartDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 5)

    DECLARE @EndDate datetime = DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 6)

    DECLARE @Type varchar(30)

    exec [up_FleetBalance_Control_Reporting_Services_Detailed] @Stations,Auto,@StartDate,@EndDate,7

    You need to add the GO batch seperator


    Clear Sky SQL
    My Blog[/url]

  • October 22, 2012 at 10:40 am

    #1551529

    Thanks a bunch, I getting this error now

    (2 row(s) affected)

    Msg 206, Level 16, State 2, Procedure up_FleetBalance_Control_Reporting_Services_Detailed, Line 0

    Operand type clash: tabletype_StationLists is incompatible with varchar

    Do I need to change it from a varchar to something else?

  • October 22, 2012 at 11:36 am

    #1551557

    You need to make sure the data type of the @Station parameter in your stored procedure is also set as 'tabletype_StationLists ' data type.

Viewing 7 posts - 1 through 6 (of 6 total)

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