Solved

sql stored procedure

Posted on 2011-03-16
19
324 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 142

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 334 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 166 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 334 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now