Dynamic SQL for SET Statements? Hate to Hard-Code it...

[smedvid]

Is it possible to develop some dynamic SQL for a SET Statement to set the value of a local variable. ??? I am stuck? Seems like it can be done.
--What I have now hard-coded; per each @CAT.
--Problem, we just expanded from 4 CATs to 25...
--Need to remove hard-coded stuff!
--I pass the @CAT Parameter value into an SP.

IF @CAT = 'SR'
BEGIN
SET @d30 = (SELECT SUM(d30) FROM tblData_SR)
SET @d60 = (SELECT SUM(d60) FROM tblData_SR)
SET @d90 = (SELECT SUM(d90) FROM tblData_SR)
SET @d120 = (SELECT SUM(d120) FROM tblData_SR)
SET @d150 = (SELECT SUM(d150) FROM tblData_SR)
SET @d180 = (SELECT SUM(d180) FROM tblData_SR)
END

ELSE IF @CAT = 'PM'
BEGIN
SET @d30 = (SELECT SUM(d30) FROM tblData_PM)
SET @d60 = (SELECT SUM(d60) FROM tblData_PM)
SET @d90 = (SELECT SUM(d90) FROM tblData_PM)
SET @d120 = (SELECT SUM(d120) FROM tblData_PM)
SET @d150 = (SELECT SUM(d150) FROM tblData_PM)
SET @d180 = (SELECT SUM(d180) FROM tblData_PM)
END

etc... for 20 more @CAT values


SET @TempString = ' tblData D_s Summary: ' +
'd30='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d30))) + ' ' +
'd60='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d60))) + ' ' +
'd90='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d90))) + ' ' +
'd120='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d120))) + ' ' +
'd150='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d150))) + ' ' +
'd180='+LTRIM(RTRIM(CONVERT(nvarchar(10),@d180))) + ' '

PRINT @TempString


Note: I do require seperate tblData's at present for each Category, due to vast number of differnt fields in each category. Not normalized but we must do what we must...



Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[RiverGuy]

Well, you're never going to get out of this maintenance free since you are obviously adding new tables, which requires some DDL work. So you might want to think about creating and maintaining a view which UNIONs all of your tblData tables together. You can add a category column with the values 'SR', 'PM', etc. Your stored procedure can then utilize code like: SET @d30 = (SELECT SUM(d30) FROM v_tblData WHERE Cat = @Cat)

 

[ousoonerjoe]

Gonna have to agree with RiverGuy here. Merging all tables together and having a single [Cat] column would be the best route.

If all the related tables have a simple naming scheme like "tblData_" then category abbreviation, then theoretically you could build a dynamic query iterating through the sys.tables where TableName LIKE 'tblData_%'. It's not the most ideal way to go, but could get you by till you can redesign the tables as mentioned previously.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[smedvid]

thanks for the advice...
Hard when you assume another's work...
I decided to create a cursor that performs a single SQL and dynamically build the SQL with the CAT for tblData_. Seems to work... and is faster...


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...