[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql stored procedure

Posted on 2011-03-16
19
Medium Priority
?
342 Views
Last Modified: 2012-05-11
hey guys, i need to know if i can sent a sql statement to the stored procedure?

exsample:  select all from login

i want to pass the statement to stored procedure, like i want to pass it like a prameter?
0
Comment
Question by:JCWEBHOST
  • 7
  • 5
  • 4
  • +2
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35146156
you could do that, but what "problem" do you try to solve with this?

create procedure run_some_sql @sql
as
 exec(@sql) 

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 1336 total points
ID: 35146164
You can, but you will need to use sp_executesql if you want to execute it since it will be classed as a dynamic SQL string.
CREATE PROCEDURE prDoStuff
	@QueryToRun	NVARCHAR(2000)
AS
BEGIN
	SET NOCOUNT ON;

	EXEC sp_executesql @QueryToRun;
END
GO

Open in new window

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35146177
Hi,

you got the answer but basically why you wanted to do so??
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JCWEBHOST
ID: 35146216
i am getting this output

Running [dbo].[SQL_QUERY] ( @SQL = select * from login ).

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[SQL_QUERY].


for this query


ALTER PROCEDURE dbo.SQL_QUERY
@SQL as varchar(max)
AS
BEGIN
      SET NOCOUNT ON;
      EXEC sp_executesql @SQL
END
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35146232
If you notice, the param in my sample is declared as NVARCHAR, not VARCHAR.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35146236
run it like this

 [dbo].[SQL_QUERY] ( @SQL = 'select * from login ').
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35146238
Hi,

you need to pass parameter with in single quote

Running [dbo].[SQL_QUERY] ( @SQL = 'select * from login' ).
0
 

Author Comment

by:JCWEBHOST
ID: 35146264
not working

output:

Running [dbo].[SQL_QUERY] ( @SQL = select * from login ).

Could not find stored procedure 's'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[SQL_QUERY].

stored procedure

ALTER PROCEDURE dbo.SQL_QUERY
@SQL as  NVARCHAR
AS
BEGIN
      SET NOCOUNT ON;
      EXEC sp_executesql @SQL
END


input paramenter:
select * from login
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35146272
run it with single quotes

Running [dbo].[SQL_QUERY] ( @SQL = 'select * from login' ).
0
 

Author Comment

by:JCWEBHOST
ID: 35146285
i did the close quotes

this is the output

Running [dbo].[SQL_QUERY] ( @SQL = 'select * from login' ).

Unclosed quotation mark after the character string ''.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[SQL_QUERY].
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35146303
Wow, this is dragging out a while for a simple question.
// create the procedure
CREATE PROCEDURE prDoStuff
	@QueryToRun	NVARCHAR(2000)
AS
BEGIN
	SET NOCOUNT ON;
	EXEC sp_executesql @QueryToRun;
END
GO

// execute it
EXEC prDoStuff 'select * from login'

Open in new window

0
 

Author Comment

by:JCWEBHOST
ID: 35146318
i tried that and it gave me this output

Running [dbo].[SQL_QUERY] ( @QueryToRun = 'select * from login' ).

Incorrect syntax near 'select * from login'.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[SQL_QUERY]
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35146336
It a stored procedure, not a function. Lose the brackets:
EXEC [dbo].[SQL_QUERY] @QueryToRun = 'select * from login'

Open in new window

0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 664 total points
ID: 35146365
// create the procedure
CREATE PROCEDURE prDoStuff
      @QueryToRun      NVARCHAR(2000)
AS
BEGIN
      SET NOCOUNT ON;
      EXEC (@QueryToRun);
END
GO

// execute it
EXEC prDoStuff 'select * from login'


refer

http://sqlserverpedia.com/wiki/Stored_Procedures_-_Executing_%26_Dynamic_SQL
0
 

Author Comment

by:JCWEBHOST
ID: 35146377
like this?


ALTER PROCEDURE dbo.SQL_QUERY
@QueryToRun      NVARCHAR(2000)
AS
      EXEC @QueryToRun
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35146394
yes , try that one
0
 

Author Comment

by:JCWEBHOST
ID: 35146406
it still give an erorr
0
 

Author Comment

by:JCWEBHOST
ID: 35146416
it work with out the quotes
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1336 total points
ID: 35146421
Where are you trying to run this from? If you're in Management Studio open a new query window and paste the following in and run it, this precisely, do not change anything, you can delete it again afterwards:
// create the procedure
CREATE PROCEDURE prDoStuff
	@QueryToRun	NVARCHAR(2000)
AS
BEGIN
	SET NOCOUNT ON;
	EXEC sp_executesql @QueryToRun;
END
GO

Open in new window

That will create the stored procedure. The clear the query window and run the following command, again this precisely, do not change anything:
EXEC prDoStuff 'select * from login'

Open in new window

And tell us what happens.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question