?
Solved

sql stored procedure

Posted on 2011-03-16
19
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 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