Declare int, float, decimal for dynamic quiries

Hi Experts, is there a different way of declaring int, float etc. for arithmetic operations? Using the code below I get 2 errors;

Error 1: Must declare the scalar variable "@multiplier"

DECLARE @SQL nvarchar(4000)
DECLARE @SQL1 nvarchar(4000)
DECLARE @multiplier int
SET @multiplier = 2
SET @SQL1='select 32*@multiplier'
SET @SQL=@SQL1
EXEC sp_executesql @SQL

Open in new window


Error 2: Conversion failed when converting the varchar value 'select 32* ' to data type int.

DECLARE @SQL nvarchar(4000)
DECLARE @SQL1 nvarchar(4000)
DECLARE @multiplier int
SET @multiplier = 2
SET @SQL1='select 32* '+ @multiplier +')'
SET @SQL=@SQL1
EXEC sp_executesql @SQL

Open in new window

LVL 1
David CIT Projects ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Your T-SQL looks fine.  Double-click on the error message and watch the cursor jump to the offending line, then copy-paste that line into this question.

btw, this is a little shorter...
DECLARE @multiplier int = 2

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Error 2:  You can't concatenate a nvarchar with an int without casting the int as a nvarchar.
Apples to apples.

SET @SQL1='select 32* '+ CAST(@multiplier as nvarchar(100)) +')'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David CIT Projects ManagerAuthor Commented:
Fantastic! Thank you Jim
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.