Dynamic Table Name - Set Variable

[smedvid]

Help - I am trying to create a dynamic SQL in an SP. Appears simple enough... Just want to set a variable to a SUM() from a Table. Thanks in Advance!

DECLARE	@LD_Total_UPB FLOAT, @LD_tblCalc NVARCHAR(25), @SQL NVARCHAR(4000)

SET @LD_tblCalc = 'tblCalc_001'
SET @LD_Total_UPB = EXEC('SELECT SUM(UPB) FROM ' + @LD_tblCalc)

--Failed Attempts
--SET @SQL = 'SELECT @LD_Total_UPB = SUM(UPB) FROM [' 
--+ @LD_tblCalc + ']'
--PRINT @SQL
--EXEC (@SQL)

PRINT @LD_Total_UPB

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

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

 

[hmckillop]

This format should help

SELECT @sql =
   N' SELECT @cnt = COUNT(*) FROM dbo.' + quotename(@tbl) +
   N' WHERE LastUpdated BETWEEN @fromdate AND ' +
   N'                           coalesce(@todate, ''99991231'')'
   SELECT @params = N'@fromdate datetime, ' +
                    N'@todate   datetime = NULL, ' +
                    N'@cnt      int      OUTPUT'
   EXEC sp_executesql @sql, @params, '20060101', @cnt = @count OUTPUT

"I'm living so far beyond my income that we may almost be said to be living apart

 

[smedvid]

Thanks!!!
Exactly what I just found online surf-in...




Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

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

 

[SQLSister]

You need to understand why dynamic SQl is a bad thing (and how to mitigate the worst of it)

http://www.sommarskog.se/dynamic_sql.html

Your database design appears at the heart to be flawed. Inthe first place, never ever under any circumstances use float for any value in which you intend to do calculations!!! Float can give incorrect results as it is not an exact datatype.

Second from your tablename and the fact that you need to use dynamic SQL to do a simple task, I would be willing to bet you have broken out things into separate tables that should in one table (example monthly tables showing expenditures). This will cause all sorts of havoc when
you need to group that data together and will result in the horror of having to dynamically create everything associated with the tables. Far better to consider if the design you have is workable over time and fix it now before it gets out of hand.



"NOTHING is more important in a database than integrity." ESquared

 

[smedvid]

Correct, a better design may eleviate the need to use dynamic SQL! Unfortunately, I have 1 day to convert a MS Access process into T-SQL; and do not have time to re-engineer entire process. The risks have been noted; and accepted by business owners.

Thanks for the comments and suggestions on FLOAT. I did not realize FLOAT was prone to errors.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

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

 

[danvlas]

The risks have been accepted by business owners"...

Yeah right... As if they knew anything about it...

Been there, done that. When it goes bad, business owners will suddenly have a memory problem, namely amnesia. Guess who will get the boot then?

You have one day to do what? Change a cheap piece of junk (coded in Access) into an expensive piece of junk (same crap coded in SQL Server)?
Failure is not a risk here, it is certainty.

I don't want to hurt anyone's feelings, but your reasons to follow this path are wrong.


Daniel Vlas
Systems Consultant

 

[smedvid]

Thanks for the feedback. Feedback is certainly welcome, albeit positive or negative. Certainly reinforces my initial position; but time constraints can not be overridden at present.

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

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

 

[SQLSister]

JUst to protect yourself, you should make sure the risks are in wirting and provided to the owners and ask them to physically sign off that they have seen the risk desciption and agreed to take the risk. Funny how when that risk is in writing and they have to sign, they are much more concerned with whether there really is a risk.

"NOTHING is more important in a database than integrity." ESquared

 

[smedvid]

Good suggestion... Thanks for the help again...


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

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