• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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