• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

sql stored procedure

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
JCWEBHOST
Asked:
JCWEBHOST
  • 7
  • 5
  • 4
  • +2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Bhavesh ShahLead AnalysistCommented:
Hi,

you got the answer but basically why you wanted to do so??
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JCWEBHOSTAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
If you notice, the param in my sample is declared as NVARCHAR, not VARCHAR.
0
 
Pratima PharandeCommented:
run it like this

 [dbo].[SQL_QUERY] ( @SQL = 'select * from login ').
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

you need to pass parameter with in single quote

Running [dbo].[SQL_QUERY] ( @SQL = 'select * from login' ).
0
 
JCWEBHOSTAuthor Commented:
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
 
Pratima PharandeCommented:
run it with single quotes

Running [dbo].[SQL_QUERY] ( @SQL = 'select * from login' ).
0
 
JCWEBHOSTAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
JCWEBHOSTAuthor Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
It a stored procedure, not a function. Lose the brackets:
EXEC [dbo].[SQL_QUERY] @QueryToRun = 'select * from login'

Open in new window

0
 
Pratima PharandeCommented:
// 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
 
JCWEBHOSTAuthor Commented:
like this?


ALTER PROCEDURE dbo.SQL_QUERY
@QueryToRun      NVARCHAR(2000)
AS
      EXEC @QueryToRun
0
 
Pratima PharandeCommented:
yes , try that one
0
 
JCWEBHOSTAuthor Commented:
it still give an erorr
0
 
JCWEBHOSTAuthor Commented:
it work with out the quotes
0
 
Carl TawnSystems and Integration DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now