"Dib" <dNOSPAMshahene@conNOSPAMsoftware.com> wrote in message
news:uRETsQ%23rFHA.2212@TK2MSFTNGP15.phx.gbl...
> Hi,
>
> Here is my SP
>
> I  need to run this as this
>
> ConFirmOrders_SP 'SVR,UCC' this is giving me
>
> Server: Msg 207, Level 16, State 3, Line 1
> Invalid column name 'UCC'.
> Server: Msg 207, Level 16, State 1, Line 1
> Invalid column name 'SVR'.
>
>
> ALTER       Procedure ConfirmOrders_sp
> (
> @StrType Varchar(100)
> )
> As
> BEGIN
> SET NOCOUNT ON
>
> Create table #Confirm
> (
> ShipName Varchar(80),
> Reference Varchar(60),
> ReqNo int,
> CorpName varchar(255),
> ReqDate datetime,
> RptType nVarchar(24)
> )
> Declare @SQL  varchar(5000)
>
> Insert into #Confirm(ShipName,Reference,ReqNo,CorpName,ReqDate,RptType)
> SELECT  Sh.ShiptoName , Req.RefNo , Req.Reqno ,
> Case Sm.ItemGroup
> When  'UCC'  Then Req.Debtor
> When 'SVR'  Then Req.Respecting
> When  'SAT'  Then Req.CorpName
> When 'SRO'  Then Req.CorpName
> When  'TRO'  Then Req.CorpName
> When  'REG'  Then Req.CorpName
> End As [Name],Req.Reqdate,SM.ItemGroup As [Req Type]
>
> FROM   dbo.tblArShipTo Sh INNER JOIN
> dbo.tblCorpRpt Req ON Sh.CustId = Req.CustId INNER JOIN dbo.tblSmItem SM
> ON Req.ReqType = SM.ItemCode
>
> WHERE Req.Reqstatus ='W' AND Sh.ShiptoId ='MAIL'
> Order By Req.Reqno
>
> Set @SQL=
> 'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
> ReqDate AS [Req Date],RptType AS [Rpt Type] from #Confirm WHERE Rpttype IN
> (' + @StrType + ')'
>
>
> Exec(@SQL)
>
> END
>
>

"Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message
news:OWca8W%23rFHA.908@tk2msftngp13.phx.gbl...
> SVR and UCC are individual strings so they both need to be quoted
> individually.  Change your call to something like this:
>
> ConFirmOrders_SP '''SVR'',''UCC'''
>
> You'll need to double check that I have the quotes done correctly, but you
> should get the idea.
>
> --
> --Brian
> (Please reply to the newsgroups only.)
>
>
> "Dib" <dNOSPAMshahene@conNOSPAMsoftware.com> wrote in message
> news:uRETsQ%23rFHA.2212@TK2MSFTNGP15.phx.gbl...
>> Hi,
>>
>> Here is my SP
>>
>> I  need to run this as this
>>
>> ConFirmOrders_SP 'SVR,UCC' this is giving me
>>
>> Server: Msg 207, Level 16, State 3, Line 1
>> Invalid column name 'UCC'.
>> Server: Msg 207, Level 16, State 1, Line 1
>> Invalid column name 'SVR'.
>>
>>
>> ALTER       Procedure ConfirmOrders_sp
>> (
>> @StrType Varchar(100)
>> )
>> As
>> BEGIN
>> SET NOCOUNT ON
>>
>> Create table #Confirm
>> (
>> ShipName Varchar(80),
>> Reference Varchar(60),
>> ReqNo int,
>> CorpName varchar(255),
>> ReqDate datetime,
>> RptType nVarchar(24)
>> )
>> Declare @SQL  varchar(5000)
>>
>> Insert into #Confirm(ShipName,Reference,ReqNo,CorpName,ReqDate,RptType)
>> SELECT  Sh.ShiptoName , Req.RefNo , Req.Reqno ,
>> Case Sm.ItemGroup
>> When  'UCC'  Then Req.Debtor
>> When 'SVR'  Then Req.Respecting
>> When  'SAT'  Then Req.CorpName
>> When 'SRO'  Then Req.CorpName
>> When  'TRO'  Then Req.CorpName
>> When  'REG'  Then Req.CorpName
>> End As [Name],Req.Reqdate,SM.ItemGroup As [Req Type]
>>
>> FROM   dbo.tblArShipTo Sh INNER JOIN
>> dbo.tblCorpRpt Req ON Sh.CustId = Req.CustId INNER JOIN dbo.tblSmItem SM
>> ON Req.ReqType = SM.ItemCode
>>
>> WHERE Req.Reqstatus ='W' AND Sh.ShiptoId ='MAIL'
>> Order By Req.Reqno
>>
>> Set @SQL=
>> 'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
>> ReqDate AS [Req Date],RptType AS [Rpt Type] from #Confirm WHERE Rpttype
>> IN
>> (' + @StrType + ')'
>>
>>
>> Exec(@SQL)
>>
>> END
>>
>>
>
>