I want to write call statement in SQL server

Question

1.36/5 (3 votes)

See more:

SQL-Server-2008R2

Getting Some error
Here my mysql Code i want to write in sql server

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_CallStatement`
(
IN vStmt VARCHAR(1000)
)
BEGIN
SET @sql=vStmt;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END$$

DELIMITER ;

//error/
Msg 137, Level 15, State 1, Procedure sp_CallStatement, Line 8
Must declare the scalar variable "@sql".
Msg 102, Level 15, State 1, Procedure sp_CallStatement, Line 11
Incorrect syntax near 'PREPARE'.

What I have tried:

CREATE PROCEDURE [dbo].[sp_CallStatement]
@Stmt VARCHAR(1000)
as
BEGIN
SET @sql=vStmt;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END

Posted 16-Feb-18 5:48am

Member 11810984

Updated 1-Apr-19 1:27am

v2

Add a Solution

Comments

RedDk 16-Feb-18 12:55pm

Using MSSQL, somewhere in the lines above, there would be a DECLARE @sql. As for "mysql" ... what's that?

2 solutions

Solution 2

--This one is working

CREATE PROCEDURE [dbo].[sp_CallStatement]
@Stmt nvarchar(max)
as
BEGIN
SET NOCOUNT ON;
Declare @sql nvarchar(max)
SET @sql=@Stmt
EXECUTE @Stmt;
END
GO

Posted 1-Apr-19 1:28am

Member 14207397

Updated 1-Apr-19 2:28am

v3

Comments

Nirav Prabtani 1-Apr-19 7:32am

+5

CHill60 1-Apr-19 11:12am

What is the point of having @sql at all in this SP? It's never used

Member 14207397 1-Apr-19 15:41pm

-- Syntax for SQL Server(so took @sql)
-- Declare the variable to be used.
DECLARE @MyCounter int;

-- Initialize the variable.
SET @MyCounter = 0;--(Set their values)
local variable named @sql to retrive value

Richard Deeming 4-Apr-19 13:38pm

As pointed out in solution 1, this leaves your database wide open to SQL injection.

Stored procedures alone will not protect you!

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
PHP: SQL Injection - Manual[^]

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-02-16T05:58:00

Msg 137, Level 15, State 1, Procedure sp_CallStatement, Line 8
Must declare the scalar variable "@sql".

As the error message says: you don't declare @sql so it doesn't know what to do with it.

Msg 102, Level 15, State 1, Procedure sp_CallStatement, Line 11
Incorrect syntax near 'PREPARE'.

PREPARE is not an SQL keyword: it doesn't know what you mean: Reserved Keywords (Transact-SQL) | Microsoft Docs[^]
Prepare is a PHP construct, not SQL. Which leaves your SP wide open to SQL injection all over again...