Concatenation of variable in Stored Proc

[pcdaveh]

I have a stored proc with some variables being passed in. The variable that I'm having difficulty with is a string or nvarchar. You'll see the variable and the location of where it needs to be placed highlighted in blue. I just need to know how to properly place the variable in the Stored Proc.

CREATE PROCEDURE upBertieTab @BeginDate Datetime, @EndDate Datetime,@Chain nvarchar AS
--@CBO nvarchar,@CBO nvarchar, @ServiceLine nvarchar
SELECT dbo.tblStatsHistory_MonthEnds.StatsDateToday, dbo.vChains_Sort_Asc.CHAIN, dbo.vCBO_Sort_Asc.CBO,
dbo.[Service Line Master].[Service Line ID], dbo.tblStatsHistory_MonthEnds.[Client No], dbo.tblStatsHistory_MonthEnds.[Client Name],
dbo.tblStatsHistory_MonthEnds.[MTD Placement No], dbo.tblStatsHistory_MonthEnds.[MTD Placement Dollars],
dbo.tblStatsHistory_MonthEnds.[MTD Collected Dollars], dbo.tblStatsHistory_MonthEnds.[MTD Collected Fees],
dbo.tblStatsHistory_MonthEnds.[YTD Placement No], dbo.tblStatsHistory_MonthEnds.[YTD Placement Dollars],
dbo.tblStatsHistory_MonthEnds.[YTD Collected Dollars], dbo.tblStatsHistory_MonthEnds.[YTD Collected Fees],
dbo.tblStatsHistory_MonthEnds.Daily_Placements, dbo.tblStatsHistory_MonthEnds.Daily_Dollars, dbo.tblStatsHistory_MonthEnds.Daily_Collections,
dbo.tblStatsHistory_MonthEnds.Daily_Fees, dbo.tblStatsHistory_MonthEnds.ProgramGenerated, dbo.tblStatsHistory_MonthEnds.RecordDate,
dbo.[Client Master].[Client Name] AS Expr1, dbo.[Service Line Master].[Service Line Description]
FROM dbo.tblStatsHistory_MonthEnds INNER JOIN
dbo.[Client Master] ON dbo.tblStatsHistory_MonthEnds.[Client No] = dbo.[Client Master].[Client No] INNER JOIN
dbo.vCBO_Sort_Asc ON dbo.[Client Master].CBO = dbo.vCBO_Sort_Asc.CBO INNER JOIN
dbo.vChains_Sort_Asc ON dbo.[Client Master].Chain = dbo.vChains_Sort_Asc.CHAIN INNER JOIN
dbo.[Service Line Master] ON dbo.[Client Master].[Service Line] = dbo.[Service Line Master].[Service Line ID]
WHERE (dbo.tblStatsHistory_MonthEnds.StatsDateToday BETWEEN CONVERT(DATETIME, @BeginDate, 102) AND CONVERT(DATETIME,@EndDate, 102)) AND (dbo.vChains_Sort_Asc.CHAIN LIKE N'%') AND (dbo.vCBO_Sort_Asc ) AND
(dbo.[Service Line Master].[Service Line ID] LIKE N'%')
ORDER BY dbo.vCBO_Sort_Asc.CBO

 

[jbenson001]

CREATE PROCEDURE <procedure_name>
<@param1> <datatype_for_param1>
<@param2> <datatype_for_param2> .. etc

AS

procedure code here...

 

[pcdaveh]

I thank you for your reply. However, If you look at the text of the stored procedure I have above you'll note that I know how to pass in and declare variables. I need to place the blue highlight variable in place of the key word
LIKE N'%' also highlight in blue. Just placing the variable in the statement fails.

 

[amrita418]

I haven't had to pass a variable of type nvarchar before.

Did you try ?
dbo.vChains_Sort_Asc.CHAIN LIKE N'%' + @chain

Regards,
AA

 

[gmmastros]

I would like to add that whenever you use nvarchar, varchar, nchar or char, you should ALWAYS specify the length of the variable.

CREATE PROCEDURE upBertieTab  
    @BeginDate Datetime, 
    @EndDate Datetime,
    @Chain nvarchar[red](100)[/red] AS
...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

You can always use the execute command to execute a prepared string that evaluates to a valid sequence of Transact_SQL commands, for example

EXEC ('SELECT * FROM ' + @tablename)

 

[pcdaveh]

Thank you all I finally got it to work with a combination of all your suggestions!

 

[BobRodes]

Care to publish your result? I'd like to see it.

Bob