parameter in four-partname tables
-
Is it possible to use parameters inside a tablename?
Like this:
SELECT * FROM database.dbo.@parameter_for_tablename WHERE 'something' = 'anything'
Thanks in advance.
-
You will probably need to use Dynamic SQL (either EXECUTE @SQL or sp_executesql) . But, please read the following before you attempt this.
http://www.sommarskog.se/dynamic_sql.html 'The Curse and Blessings of Dynamic SQL'
I wasn't born stupid - I had to study.
-
In what context do you need to do this??
Cause the answer can change from you need to go back to school or you need to use this admin sp
. -
Thanks, this helped me.
I tried to keep the code minimal, but it seems like its not really possible when doing this.
Btw, It's on a closed intranet server and this is open to sql-injection, but minimises the coding (1 sp instead of loads)
Thanks!
-
Just my 2 cents... trading security for simple coding is rarely a good trade off.
-
It may well benefit you to give us the context, etc. rgR'us has a 'pretty' good track record.
"Any society that would give up a little liberty to gain a little security will deserve neither and lose both. "
Benjamin Franklin
I wasn't born stupid - I had to study.
-
"rgR'us has a 'pretty' good track record"
Hey I'm only half borg... so I'm gonna do a mistake once in a while
.Nice quote
. -
I thought I'd get a rise out of you saying 'pretty'. Its actually an excellent track record.
I wasn't born stupid - I had to study.
-
Thanx... borgs are not satisfied with anything less than perfection
. -
Here is the SP:
CREATE PROCEDURE sp_inventering_lista
@RESENH varchar(255)
AS
SELECT
subquery.Resultatenhet,
subquery.ARTNR,
subquery.BENAEMN,
subquery.ILAGER,
subquery.RESANT,
subquery.DISPONIBELT,
SUM(subquery.SALDO)+DISPONIBELT AS SALDO,
SUM(subquery.SALDO)+SUM(inventering.dbo.hagfors.antal) as SALDO_INVENTERING
FROM
(SELECT
CASE dbo.ARTRAD.RESENH
WHEN 'Ar' THEN 'Arvika'
WHEN 'Ha' THEN 'Hagfors'
WHEN 'Kd' THEN 'Karlstad'
WHEN 'Su' THEN 'Sunne'
WHEN 'To' THEN 'Torsby'
END AS Resultatenhet,
dbo.ARTRAD.ARTNR,
dbo.ART.BENAEMN,
SUM(dbo.ARTRAD.ANTAL1) AS ANTAL,
dbo.ART.ILAGER,
dbo.ART.RESANT,
dbo.ART.ILAGER - dbo.ART.RESANT AS DISPONIBELT,
-- Beräkna faktiskt lagervärde
CASE dbo.ARTRAD.TYP
WHEN 'F' THEN -SUM(dbo.ARTRAD.ANTAL1)
WHEN 'LF' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN 'LS' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN 'MI' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN 'MU' THEN -SUM(dbo.ARTRAD.ANTAL1)
ELSE 0
END AS SALDO
FROM dbo.ARTRAD INNER JOIN dbo.ART ON dbo.ARTRAD.ARTNR = dbo.ART.ARTNR
WHERE
(dbo.ARTRAD.MAKUL <> 1) AND
(dbo.ARTRAD.DAT BETWEEN '2005-08-01' AND GETDATE()) AND
(dbo.ARTRAD.RESENH = @RESENH) AND
(NOT (dbo.ARTRAD.ANTAL1 IS NULL)) AND
(NOT (dbo.ARTRAD.TYP ='OF')) AND
(NOT (dbo.ARTRAD.TYP ='B')) AND
(NOT (dbo.ARTRAD.TYP ='O'))
GROUP BY
dbo.ARTRAD.RESENH,
dbo.ARTRAD.ARTNR,
dbo.ART.BENAEMN,
dbo.ART.ILAGER,
dbo.ART.RESANT,
dbo.ARTRAD.TYP) AS subquery
-- left outer join
RIGHT OUTER JOIN inventering.dbo.hagfors ON subquery.ARTNR COLLATE DATABASE_DEFAULT = inventering.dbo.hagfors.artnr COLLATE DATABASE_DEFAULT
GROUP BY
subquery.Resultatenhet,
subquery.ARTNR,
subquery.BENAEMN,
subquery.ILAGER,
subquery.RESANT,
subquery.DISPONIBELT
--inventering.dbo.hagfors.artnr
ORDER BY
subquery.ARTNR
GO
I want that table (in bold) to be parameterized...
-
Well that's another story, why can't this data be kept in the same table??
Or are you querying different data in different tables?
-
The data needs to be separated im afraid, so im querying different tables.
Thats why I want one SP which are general for all tables (which have same structure), instead of many SP:s which makes it quite a burden to update...
-
This should do what you want:
CREATE PROCEDURE sp_inventering_lista
@RESENH varchar(255), @TableParam varchar(100)
AS
declare
@SQL varchar(8000)
select @SQL =
'SELECT
subquery.Resultatenhet,
subquery.ARTNR,
subquery.BENAEMN,
subquery.ILAGER,
subquery.RESANT,
subquery.DISPONIBELT,
SUM(subquery.SALDO)+DISPONIBELT AS SALDO,
SUM(subquery.SALDO)+SUM(inventering.dbo.hagfors.antal) as SALDO_INVENTERING
FROM
(SELECT
CASE dbo.ARTRAD.RESENH
WHEN ''Ar'' THEN ''Arvika''
WHEN ''Ha'' THEN ''Hagfors''
WHEN ''Kd'' THEN ''Karlstad''
WHEN ''Su'' THEN ''Sunne''
WHEN ''To'' THEN ''Torsby''
END AS Resultatenhet,
dbo.ARTRAD.ARTNR,
dbo.ART.BENAEMN,
SUM(dbo.ARTRAD.ANTAL1) AS ANTAL,
dbo.ART.ILAGER,
dbo.ART.RESANT,
dbo.ART.ILAGER - dbo.ART.RESANT AS DISPONIBELT,
-- Beräkna faktiskt lagervärde
CASE dbo.ARTRAD.TYP
WHEN ''F'' THEN -SUM(dbo.ARTRAD.ANTAL1)
WHEN ''LF'' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN ''LS'' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN ''MI'' THEN SUM(dbo.ARTRAD.ANTAL1)
WHEN ''MU'' THEN -SUM(dbo.ARTRAD.ANTAL1)
ELSE 0
END AS SALDO
FROM dbo.ARTRAD INNER JOIN dbo.ART ON dbo.ARTRAD.ARTNR = dbo.ART.ARTNR
WHERE
(dbo.ARTRAD.MAKUL 1) AND
(dbo.ARTRAD.DAT BETWEEN ''2005-08-01'' AND GETDATE()) AND
(dbo.ARTRAD.RESENH = @RESENH) AND
(NOT (dbo.ARTRAD.ANTAL1 IS NULL)) AND
(NOT (dbo.ARTRAD.TYP =''OF'')) AND
(NOT (dbo.ARTRAD.TYP =''B'')) AND
(NOT (dbo.ARTRAD.TYP =''O''))
GROUP BY
dbo.ARTRAD.RESENH,
dbo.ARTRAD.ARTNR,
dbo.ART.BENAEMN,
dbo.ART.ILAGER,
dbo.ART.RESANT,
dbo.ARTRAD.TYP) AS subquery
-- left outer join
RIGHT OUTER JOIN inventering.dbo.'+ @TableParam + ' ON subquery.ARTNR COLLATE DATABASE_DEFAULT = inventering.dbo.hagfors.artnr COLLATE DATABASE_DEFAULT
GROUP BY
subquery.Resultatenhet,
subquery.ARTNR,
subquery.BENAEMN,
subquery.ILAGER,
subquery.RESANT,
subquery.DISPONIBELT
--inventering.dbo.hagfors.artnr
ORDER BY
subquery.ARTNR
GO'
exec (@SQL)
-
Ya that should do it... but I rarely saw a system where the tables HAD TO be separated. Can we hear the reason behind this split?
-
Thanks David.
' + @table + ' was exactly was i was looking for
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply