SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Store query results in through separate Store proc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

manand79
Starting Member

India
8 Posts

Posted - 07/29/2013 :  01:20:53  Show Profile  Reply with Quote
I wanted to write a prodedure which can access any type of queries and provide the results.

For ex. There are 2 query

1. Select * from emp
2. Select * from DEP

So SP can access query and provide the results without knowing the internal functionality of query.

Your prompt help is really appreciable.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/29/2013 :  02:01:31  Show Profile  Reply with Quote
sorry didnt understand what this question is about. In any case procedure will execute each query included within it and retrieve the result. Didnt understand whats the issue with internal functionality

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 07/29/2013 :  04:08:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Do you want to return multiple resultsets from the stored procedure?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  00:31:09  Show Profile  Reply with Quote
For ex. There is one separate query with retrieving employee details. My SP will access that query that provide the result.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/30/2013 :  01:18:33  Show Profile  Reply with Quote
hmm..do you mean Sp executing an external query stored somewhere may be in a table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  01:31:43  Show Profile  Reply with Quote
Exactly, Please help me on this.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/30/2013 :  01:35:44  Show Profile  Reply with Quote
quote:
Originally posted by manand79

Exactly, Please help me on this.



For that you need dynamic sql

something like

CREATE PROC procname
AS
...
othercode

DECLARE @SQLQuery varchar(8000)

SELECT @SQLQuery = SQLColumn
FROM Table

EXEC (@SQLQuery)
...
GO


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  01:49:02  Show Profile  Reply with Quote
I am fresher and not able to find solution. Can you please send full code with example of emp table. I would really appreciate you for this.

This will help me a lot.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/30/2013 :  01:57:57  Show Profile  Reply with Quote
can you post structure of table storing the query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  02:53:37  Show Profile  Reply with Quote
EmpId int
Emp Name varchar(30)
Designation Varchar(30)
DateOfBirth Datetime
Address Varchar(500)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/30/2013 :  03:53:29  Show Profile  Reply with Quote
hmm...which field stores the query here? I dont think this is table containing the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  04:08:40  Show Profile  Reply with Quote
Okay.. Can you please take any dummy table at your convenience. They have only given me this scenario and ask for output.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/30/2013 :  05:08:13  Show Profile  Reply with Quote

--table storing queries
create table querytable
(
id int identity(1,1),
query varchar(2000)
)

--main table having the data
create table maintable
(
id int identity(1,1),
val1 varchar(100),
)


insert maintable (val1)
values ('test1'),
('test2'),
('test5'),
('test6'),
('test7')


insert querytable (query)
values ('select * from maintable')

DECLARE @Query varchar(2000)

SELECT @Query = query
FROM querytable

EXEC(@Query)


output
---------------------
id	val1
---------------------
1	test1
2	test2
3	test5
4	test6
5	test7



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 07/30/2013 :  13:26:24  Show Profile  Reply with Quote
Error is coming while I am runnig above code. Error saying "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ','."
Error is coming on
insert maintable (val1)
values ('test1'),

Please note i am using sql server 2008. Look forward to your positive response.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/31/2013 :  02:00:43  Show Profile  Reply with Quote
quote:
Originally posted by manand79

Error is coming while I am runnig above code. Error saying "Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ','."
Error is coming on
insert maintable (val1)
values ('test1'),

Please note i am using sql server 2008. Look forward to your positive response.


if using sql 2008 then it should work. if before, use below modification


--table storing queries
create table querytable
(
id int identity(1,1),
query varchar(2000)
)

--main table having the data
create table maintable
(
id int identity(1,1),
val1 varchar(100),
)


insert maintable (val1)
select 'test1' union all
select 'test2' union all
select 'test5' union all
select 'test6' union all
select 'test7'


insert querytable (query)
values ('select * from maintable')

DECLARE @Query varchar(2000)

SELECT @Query = query
FROM querytable

EXEC(@Query)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

manand79
Starting Member

India
8 Posts

Posted - 08/06/2013 :  06:13:18  Show Profile  Reply with Quote
Hi, They are not satisfied with answer. Can you please help me.

As per them, below is table structure which store id and query. I have to write store procedure which take id as parameter and display query result from table.

Table Structure

Id Query
1 Select * from emp
2 select * from dept
3 select * from job

Look forward to your quick response.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/06/2013 :  06:23:10  Show Profile  Reply with Quote

CREATE PROC ExecQuery
@Id int
AS
DECLARE @Query varchar(2000)

SELECT @Query = Query
FROM Table
WHERE ID = @Id
EXEC(@Query)
GO

call it like


EXEC ExecQuery @Id=3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 08/18/2013 :  10:02:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You need to read this www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000