April 19, 2012 at 9:27 am
hi
i'm trying to change a left join string based on a parameter.
..this is how i build the string...
ALTER PROC dbo.SAM_RPT_param (@YR INT, @MTH INT, @div VARCHAR(25), @FOC VARCHAR(7))
AS
DECLARE @FOC_SQL_1 AS VARCHAR(1000);
DECLARE @FOC_SQL_2 AS VARCHAR(1000);
DECLARE @FOC_SQL_3 AS VARCHAR(1000);
DECLARE @FOC_SQL_4 AS VARCHAR(1000);
IF @FOC = 'ALL'
-- PROD GRP MTD
SET @FOC_SQL_1 = '(SELECT InvYear, InvMthNo, ProdGrpCode as StkCode, SUM(InvQty) InvQty, SUM(InvVal) InvVal, SUM(InvCost) InvCost, SUM(ProfMargin) ProfMargin
FROM dbo.tbl_PROTEUS_dddsales_import_02
WHERE InvYear = @YR AND InvMthNo = @MTH AND HomeExport = @div
GROUP BY InvYear, InvMthNo, ProdGrpCode) AS actMtd
ON prod.[Year] = actMtd.InvYear and prod.[Month] = actMtd.InvMthNo and prod.ProdGrpCode = actMtd.StkCode'
... this is how i use it...
left Join @FOC_SQL_1
.. but i keep getting an error..
Msg 1087, Level 15, State 2, Procedure SAM_RPT_param, Line 78
Must declare the table variable "@FOC_SQL_1".
am i doing something wrong??
thanks
April 19, 2012 at 11:05 am
You can't join or select from a variable like that. Build the string then EXEC that like
EXEC ('Select * from ' + @FOC_SQL_1)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 19, 2012 at 11:30 am
Probably something like this:
DECLARE @Query1 Varchar(1000)
DECLARE @Query2 Varchar(1000)
DECLARE @FinalQuery Varchar(max)
SET @Query1 = 'SELECT * FROM Employees A'
SET @Query2 = 'SELECT EmpDept, EmpID FROM EmpDepartments'
SET @FinalQuery = @Query1 + ' LEFT OUTER JOIN (' + @Query2 + ') B ON A.EmpID = B.EmpID'
======================================
Blog: www.irohitable.com
April 19, 2012 at 2:24 pm
The desire to use of an object name stored in a variable to build a query, e.g.
DECLARE @tableName SYSNAME;
SET @tableName = N'MyTable';
SELECT Column
FROM @tableName;
usually indicates a flawed design. Mind telling us why you want to do such things? Maybe we can help you in your design, and help you avoid some common design pitfalls.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 2:54 pm
thanks for the replies
my first table is a kind of reference table (all_dates_and_products) which i created using a cross join for years, months and joined to products so i end up with a row of every product in each year, month.
some other info....
foc = free of charge
@foc = All, then show all sales
@foc = None, then exclude foc sales
@foc = Only, then show only foc sales
alter proc my_proc (@foc varchar(5))
as
select a.year, a.month, a.product, b.sales, b.units
from all_dates_and_products a
-- my 3 options are one of the below...
-- 1) all sales
if @foc = 'all'
left join (select * from my_sales) as b on year, month, product
-- 2) exclude foc
if @foc = 'none'
left join (select * from my_sales where foc <> @foc) as b on year, month, product
-- 3) only foc
if @foc = 'only'
left join (select * from my_sales where foc = @foc) as b on year, month, product
the @foc is passed from a combo box in excel. what i need is to know how to select the correct left join statement with the correct operator.
hope this makes sense. thanks
April 19, 2012 at 3:04 pm
OK, this was not what it looked like at first glance...but there is somethign you need to know from the outset: SQL is declarative, not interpreted, so you cannot use IF-blocks to append to a query in the way you have it structured. You could use dynamic sql to do what you are looking for, but there is no need to in your case. You'll need explicitly declared logic branches for each of your queries. Also, your join sysntax may work on another DBMS, but in SQL Server you must show each predicate as tableA.column = tableB.column. See if something like this makes more sense:
ALTER PROC my_proc (@foc VARCHAR(5))
AS -- my 3 options are one of the below...
BEGIN
-- 1) all sales
IF @foc = 'all'
BEGIN
SELECT a.year,
a.month,
a.product,
b.sales,
b.units
FROM all_dates_and_products a
LEFT JOIN my_sales AS b ON a.[year] = b.[year]
AND a.[month] = b.[month]
AND a.product = b.product
END
-- 2) exclude foc
ELSE IF @foc = 'none'
BEGIN
SELECT a.year,
a.month,
a.product,
b.sales,
b.units
FROM all_dates_and_products a
LEFT JOIN my_sales AS b ON foc <> @foc
AND a.[year] = b.[year]
AND a.[month] = b.[month]
AND a.product = b.product
END
-- 3) only foc
ELSE IF @foc = 'only'
BEGIN
SELECT a.year,
a.month,
a.product,
b.sales,
b.units
FROM all_dates_and_products a
LEFT JOIN my_sales AS b ON foc = @foc
AND a.[year] = b.[year]
AND a.[month] = b.[month]
AND a.product = b.product
END
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 3:11 pm
opc.three, couldn't the where be combined like so (not checked for syntax):
select a.year, a.month, a.product, b.sales, b.units
from all_dates_and_products a
left join my_sales b on
a.year = b.year
and
a.month = b.month
and
a.product = b.product
where
b.foc = case @foc
when 'only' then @foc
when 'none' then 'none'
else b.foc
end
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 19, 2012 at 3:14 pm
hi
sorry i just abbreviated the joins for simplicity.
the problem with the way your suggesting is that the select statement has many more columns and some other joined table so doing it this way would results in pages and pages of sql.
i really wanted to keep it as short as possible????
thanks
April 19, 2012 at 3:15 pm
toddasd (4/19/2012)
opc.three, couldn't the where be combined like so (not checked for syntax):
select a.year, a.month, a.product, b.sales, b.units
from all_dates_and_products a
left join my_sales b on
a.year = b.year
and
a.month = b.month
and
a.product = b.product
where
b.foc = case @foc
when 'only' then @foc
when 'none' then 'none'
else b.foc
end
Possibly, but not in the WHERE-clause. It would have to occur in the JOIN, because it is an OUTER JOIN. I was just trying to illustrate declarative versus interpreted.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 3:17 pm
spin (4/19/2012)
hisorry i just abbreviated the joins for simplicity.
the problem with the way your suggesting is that the select statement has many more columns and some other joined table so doing it this way would results in pages and pages of sql.
i really wanted to keep it as short as possible????
thanks
Welcome to SQL 🙂
It may seem like you are writing a lot of code to do simple things, and sometimes you will, but you have options to reduce code without going to a dynamic SQL approach. Do not worry about the amount of code at first, worry about getting the correct result first, then be worried about performance...the amount of code you are writing should be way down the list.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 3:20 pm
.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 20, 2012 at 4:18 am
what i now have instead of
if A
@var = select .......
if B
@var = select .......
if C
@var = select .......
i've instead created 3 stored procs so
if A
exec proc1 val1, val2, val3
if B
exec proc2 val1, val2, val3
if C
exec proc3 val1, val2, val3
...which seems to be doing the trick. i now need to join to procs together. something like..
if A
exec proc1 val1, val2, val3
UNION
exec proc1a val1, val2, val3
...these are a monthly total (proc1) and a year to date total (proc1a). but i get a syntax error. how do i go about joining them??
thanks
April 20, 2012 at 7:23 am
spin (4/20/2012)
what i now have instead ofif A
@var = select .......
if B
@var = select .......
if C
@var = select .......
i've instead created 3 stored procs so
if A
exec proc1 val1, val2, val3
if B
exec proc2 val1, val2, val3
if C
exec proc3 val1, val2, val3
...which seems to be doing the trick. i now need to join to procs together. something like..
if A
exec proc1 val1, val2, val3
UNION
exec proc1a val1, val2, val3
...these are a monthly total (proc1) and a year to date total (proc1a). but i get a syntax error. how do i go about joining them??
thanks
I like where you're headed, but stored procedures cannot be unioned together in that way. Maybe an Inline Table-valued Function (aka parameterized view) will work for you instead of a procedure. Something like this:
CREAT FUNCTION dbo.something(@val1 INT, @val2 INT)
RETURNS TABLE
AS
RETURN (SELECT ... WHERE column = @val1 AND ... )
Then you can union the results like this:
SELECT column1, column2 FROM dbo.some_function(@some_variable, @other, @Another)
UNION ALL
SELECT column1, column2 FROM dbo.some_other_function(@some_variable, @other, @Another)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply