Question about passing a table name to a stored procedure 1

[Magnus53]

Hi everyone,

I have a quick question. I'm wanting to be able to call a stored procedure, passing it two variables that will make up part of the table name.

How do I do a string concatination so that it'll get the variable value and not the variable name?

This is part of my SQL statement, but it always throws a "invalid column prefix" error

OPS.downline_+ @monthName +_+ @yearNum +.cons_id as Consultant

I want it to look like this after the variables are passed in.

OPS.downline_aug_2003.cons_id as Consultant

Anyone know how to get this to work?

Thanks

 

[JayKusch]

declare @CommandString VarChar(400)

SELECT @CommandString = 'OPS.downline_' + @monthName + '_' + @yearNum + '.cons_id as Consultant'


Thanks

J. Kusch

 

[JayKusch]

You would then execute the code by the following statment:

exec (@CommandString)

This is called a "Dynamic SQL" Command

Thanks

J. Kusch

 

[SQLSister]

'OPS.downline_'+ @monthName +_+ @yearNum +'.cons_id' as Consultant

 

[SQLSister]

OOPs for got the "" around the _ inthe middle. But someone else got them so ignore me.

 

[Magnus53]

Hi guys thanks for the replies

I understand what you guys are suggesting, but my problem is that my query is pretty big. I'll post it in full to help explain my problem a bit more.

I have this stored procedure, that I have to make dynamic. I'm calling the procedure from an ASP page.

This is the sql statement I was given to wrk with

CREATE  PROCEDURE mgt_Report_Grace_months AS
SELECT     Stat.*
FROM  (SELECT     OPS.downline_aug_2003.cons_id AS   Consultant, MAX(CASE WHEN consultant_monthly_history.chst_yr = 3 AND 
consultant_monthly_history.chst_mth = 8 AND 
consultant_monthly_history.levl_num >= 2 THEN consultant_level.levl_name ELSE ' None' END) AS Current_Level, 
MAX(CASE WHEN consultant_monthly_history.chst_yr = 3 AND 
consultant_monthly_history.chst_mth = 7 THEN consultant_level.levl_name ELSE ' None' END) AS Prev_Level, 
ops.downline_aug_2003.down_pers_sales_amt AS Pers_Sales, ops.downline_aug_2003.down_qtd_sales_amt AS Qtd_Sales, 
ops.downline_aug_2003.down_pers_unit_sales_amt AS Unit_Sales, ops.downline_aug_2003.down_ytd_sales_amt AS Ytd_Sales, 
SUM(CASE WHEN consultant_monthly_history.chst_mth <= 8 AND consultant_monthly_history.chst_yr = 3 AND 
consultant_monthly_history.chst_grace_mth_flag = 'Y' THEN 1 ELSE 0 END) AS Grace_Month, address.addr_str as Address, Address.addr_City as City, Address.prst_Code as Province, 
Address.Addr_post_zip_code as Postal_Code, Consultant.cons_email as Email, Address.addr_ph as Phone, Consultant.Cons_Start_date as Start_Date, Month(Consultant.Cons_Start_date) as Start_Month,Day(Consultant.Cons_Start_date) as Start_Day,     consultant.cons_id_recruited_by as Recruiter
FROM   consultant_level INNER JOIN
consultant_monthly_history ON consultant_level.levl_num = consultant_monthly_history.levl_num RIGHT OUTER JOIN
OPS.downline_aug_2003 ON consultant_monthly_history.cons_id = OPS.downline_aug_2003.cons_id RIGHT outer JOIN
consultant ON consultant.cons_id = OPS.downline_aug_2003.cons_id RIGHT outer JOIN
address ON consultant.addr_num_home = address.addr_numGROUP BY OPS.downline_aug_2003.cons_id, consultant_level.levl_name, ops.downline_aug_2003.down_pers_sales_amt, 
ops.downline_aug_2003.down_qtd_sales_amt, ops.downline_aug_2003.down_pers_unit_sales_amt, 
ops.downline_aug_2003.down_ytd_sales_amt, consultant.cons_id, consultant.addr_num_home, 
consultant.cons_id_recruited_by, address.addr_str, Address.addr_City, Address.prst_Code, 
Address.Addr_post_zip_code,Consultant.cons_email,Address.addr_ph,Consultant.Cons_Start_date) AS Stat
WHERE     Current_Level <> ' None'
GO

... So I need to replace every instance of the hard coded month and year in this stored procedure. For some reason I can't get it to work at all.

Thanks for looking again

 

[JayKusch]

OK ... use this example and the replace your hard-coded text with the variables needed as described in the first couple of posts. Hope this helps ...


declare @CommandString VArChar(8000)

SELECT @CommandString =
'
SELECT Stat.*
FROM (SELECT OPS.downline_aug_2003.cons_id AS Consultant, MAX(CASE WHEN consultant_monthly_history.chst_yr = 3 AND
consultant_monthly_history.chst_mth = 8 AND
consultant_monthly_history.levl_num >= 2 THEN consultant_level.levl_name ELSE ' + '''' + ' None' + '''' + ' END) AS Current_Level,
MAX(CASE WHEN consultant_monthly_history.chst_yr = 3 AND
consultant_monthly_history.chst_mth = 7 THEN consultant_level.levl_name ELSE ' + '''' + ' None' + '''' + ' END) AS Prev_Level,
ops.downline_aug_2003.down_pers_sales_amt AS Pers_Sales, ops.downline_aug_2003.down_qtd_sales_amt AS Qtd_Sales,
ops.downline_aug_2003.down_pers_unit_sales_amt AS Unit_Sales, ops.downline_aug_2003.down_ytd_sales_amt AS Ytd_Sales,
SUM(CASE WHEN consultant_monthly_history.chst_mth <= 8 AND consultant_monthly_history.chst_yr = 3 AND
consultant_monthly_history.chst_grace_mth_flag = + ''' + 'Y' + ''' THEN 1 ELSE 0 END) AS Grace_Month, address.addr_str as Address, Address.addr_City as City, Address.prst_Code as Province,
Address.Addr_post_zip_code as Postal_Code, Consultant.cons_email as Email, Address.addr_ph as Phone, Consultant.Cons_Start_date as Start_Date, Month(Consultant.Cons_Start_date) as Start_Month,Day(Consultant.Cons_Start_date) as Start_Day, consultant.cons_id_recruited_by as Recruiter
FROM consultant_level INNER JOIN
consultant_monthly_history ON consultant_level.levl_num = consultant_monthly_history.levl_num RIGHT OUTER JOIN
OPS.downline_aug_2003 ON consultant_monthly_history.cons_id = OPS.downline_aug_2003.cons_id RIGHT outer JOIN
consultant ON consultant.cons_id = OPS.downline_aug_2003.cons_id RIGHT outer JOIN
address ON consultant.addr_num_home = address.addr_numGROUP BY OPS.downline_aug_2003.cons_id, consultant_level.levl_name, ops.downline_aug_2003.down_pers_sales_amt,
ops.downline_aug_2003.down_qtd_sales_amt, ops.downline_aug_2003.down_pers_unit_sales_amt,
ops.downline_aug_2003.down_ytd_sales_amt, consultant.cons_id, consultant.addr_num_home,
consultant.cons_id_recruited_by, address.addr_str, Address.addr_City, Address.prst_Code,
Address.Addr_post_zip_code,Consultant.cons_email,Address.addr_ph,Consultant.Cons_Start_date) AS Stat
WHERE Current_Level <> ' + '''' + ' None' + '''' +
' GO
'


exec (@CommandString)



Thanks

J. Kusch

 

[Magnus53]

Thanks for the help Jay, it now works great.