Member Avatar for weeraa

I faced to a huge problem when i coding some stored procedure. How to combine some string to sql string in stored procedure???

This is what i did upto now.

@WhereCondition = ' WHERE id = 102 '
DECLARE recoredSet CURSOR FOR
        SELECT SUM(Amount) as AmountSum FROM sale + @WhereCondition
OPEN recoredSet

--After above code I wrote fetch commands and it has no any problem.

In above code, @WhereCondition is assinged some value.
Eg: @WhereCondition = ' WHERE id = 5 '

But the problem is plus mark (+) is not supported. If Plus mark is not there, the code is working well.
My Problem is how to combined some string to a sql string like above code. I put and sign (&) instead of plus mark. But it doesnt work.

  • 3 Contributors
  • 5 Replies
  • 246 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by C#Jaap

Dani AI

Generated

Short answer: the SQL parser expects a complete SELECT when you declare a cursor, so you can’t just stick a T-SQL variable into the DECLARE ... FOR line the way you tried. is right that you need to build the query text and execute it; is also right to remind that concatenation syntax differs between engines (SQL Server uses + for string concatenation, and SQL Server 2005 has no CONCAT()).

If all you need is the aggregate value, don’t use a cursor at all — just assign the SUM to a variable with a normal parameterized WHERE:

DECLARE @id INT = 102;
DECLARE @AmountSum DECIMAL(18,2);

SELECT @AmountSum = SUM(Amount)
FROM sale
WHERE id = @id;

If the WHERE clause itself must be dynamic, use parameterized dynamic SQL (sp_executesql) so you keep parameters (and avoid SQL injection) and can return the result into an OUTPUT parameter:

DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(100);
DECLARE @id INT = 102;
DECLARE @AmountSum DECIMAL(18,2);

SET @sql = N'SELECT @AmountSumOut = SUM(Amount) FROM sale WHERE id = @idIn;';
SET @params = N'@idIn INT, @AmountSumOut DECIMAL(18,2) OUTPUT';

EXEC sp_executesql @sql, @params, @idIn = @id, @AmountSumOut = @AmountSum OUTPUT;

If you really need a cursor over a dynamic resultset, create a temporary table in the outer batch, populate it via sp_executesql (or INSERT...EXEC), then declare the cursor on that temp table. That keeps the cursor declaration static while still allowing a dynamic source.

Notes and cautions: prefer set-based solutions where possible (cursors are slow), never concatenate untrusted input into SQL text, and prefer parameterized sp_executesql calls for safety and plan reuse.

Recommended Answers

All 5 Replies

Member Avatar for pritaeas

You can only do this when you concat strings to build the query, and then execute it with sp_executesql

Member Avatar for C#Jaap

It depends on database you are using. When you use an Oracle database you should use || (double pipe) to concat, + can be used with SQL Server and for MySQL you should use Concat('A', 'B')....

Edited by C#Jaap
Member Avatar for pritaeas

Those options work for concatenating column values, not for building a query.

Member Avatar for weeraa

im using sql server 2005. It is not supported CONCAT command....

Member Avatar for C#Jaap

@Pritaes: You've got a point and is more described in: Click Here, especially Click Here
Thank you for this opportunity to learn....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.