Stored Procedures and Common Table Expressions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedures and Common Table Expressions

Author

Topic

genius_palli
Starting Member

42 Posts

Posted - 2008-11-22 : 02:36:20

Hi everyone.
I am working on a project where i need to execute MS-Access Queries into SQL. In Access,there are Crosstab queries but they are not in SQL.
Is there any way by which i can execute the stored procedure and store the results in any temporary result set( Like Common Table Expressions), so that results can be used in further queries.

As i am new to development and SQL, plz help.
Any help will be greatly appreciated... thanks...

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:44:05

Common Table Expressions might not be a good solution if wants to use data for series of queries. CTEs are for one time use. You may use temporary table for storing data and make use of PIVOT operator to get cross tabbed results.
Refer to bokks online to get syntax and usage of PIVOT

genius_palli
Starting Member

42 Posts

Posted - 2008-11-22 : 03:23:38

Hi visakh16..
Thanks for ur kind reply first of all.
Other than Temporary Tables, isnt there any other Temporary Result Set ?
I cant use PIVOT also because the compatibility level of my database is not up to that appropriate level i.e. 80 or 90.
Isnt there any other way ?
greatly confused.. Please help..

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 03:48:27

quote:
Originally posted by genius_palli

Hi visakh16..
Thanks for ur kind reply first of all.
Other than Temporary Tables, isnt there any other Temporary Result Set ?
I cant use PIVOT also because the compatibility level of my database is not up to that appropriate level i.e. 80 or 90.
Isnt there any other way ?
greatly confused.. Please help..

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

Nope. if you want your data to reused for queries that follow, its better to store them in temporary tables or table variables (if amount of data is small).
If you cant use PIVOT, you can try other cross tabbing options
Can you post some sample data to illustrate your scenario and also provide output you want.
Then i will be able to provide you with more specific solution.

genius_palli
Starting Member

42 Posts

Posted - 2008-11-22 : 04:26:17

Hi visakh16..
Thanks once again .. following is the list of MS Access queries with their names which i need to execute in sql, what i have to do is .. to create a single query for all these queries and then to represent data using .Net

--- Video/Photo Summery - Main Report (new) ---

SELECT [Referral Type].Description AS [Ref Type],
[Schedual - From SQL].*,
[Schedual - From SQL].[Deposit Date] AS [Deposit In], IIf([Level]=1,[Fee],0) AS SumFee,
[Passthru - Recordset].Pkg, [Passthru - Recordset].Digtal,
Occasion.Description AS Occ, [Passthru - Recordset].AtEvent,
[Referal Sheets - Digtal / Film].[Photo Type]
FROM (((([Schedual - From SQL]
LEFT JOIN [Referral Type] ON [Schedual - From SQL].[Referral Type] = [Referral Type].[Referral Type])
INNER JOIN [Print Numbers] ON [Schedual - From SQL].[Contractor Type] = [Print Numbers].Number)
INNER JOIN Occasion ON [Schedual - From SQL].[Occasion ID] = Occasion.[Occasion ID])
INNER JOIN [Passthru - Recordset] ON ([Schedual - From SQL].[Contractor Type] =
[Passthru - Recordset].[Contractor Type]) AND ([Schedual - From SQL].[Event ID] =
[Passthru - Recordset].[Event ID]))
LEFT JOIN [Referal Sheets - Digtal / Film] ON [Schedual - From SQL].[Event ID] =
[Referal Sheets - Digtal / Film].[Event ID];

-------- [ Passthru - Recordset ] stored procedure with two date parameters --------

P_Schedual_Pkg '11/18/2008 11:10:09 PM', '11/17/2013 11:10:09 PM'

-------- [ Referal Sheets - Digital / Film ] select query ---------------

SELECT [Transaction].[Event ID],
case Sum( case [Service ID] when 287 then 1 else 0 end ) when 1 then 'D' else 'F' end AS [Photo Type]
FROM [Transaction] where cancelled =0
GROUP BY [Transaction].[Event ID];

--------- [ Schedual - From SQL ] stored procedure with two date parameters --------

p_Schedual_Summery '11/18/2008 11:10:09 PM', '11/17/2012 11:10:09 PM'

I have tried Common Table expressions also but all in vain. Above stored procedures are already using Temporary tables, therefore i m not preferring to use them. I am looking for any other way to solve the problem.
Thanks in advance. Please suggest ..

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 04:41:10

please provide sample data and output rather than query. refer below for format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

genius_palli
Starting Member

42 Posts

Posted - 2008-11-22 : 05:24:44

Thanks visakh16...
i have visited the link .. I regret for asking questions in the wrong way..
i will try to post u sample data and output also..
Wish u a happy weekend :-)

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 11:06:39

quote:
Originally posted by genius_palli

Thanks visakh16...
i have visited the link .. I regret for asking questions in the wrong way..
i will try to post u sample data and output also..
Wish u a happy weekend :-)

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

No worries
Post data in desired format and i will give you solution

genius_palli
Starting Member

42 Posts

Posted - 2008-11-25 : 06:09:28

Hi.. everyone ...
following is a piece of code in MS Access. Could anyone please help me how we can write it in SQL.
I am using case when then , but not getting the appropriate answer.

Not IsNull([tablename]![fieldname]) as [somename]

Please assist. Thanks in advance.

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 06:37:40

may be something like
case when [tablename].[fieldname]) IS NOT NULL THEN vlaue ELSE ...

genius_palli
Starting Member

42 Posts

Posted - 2008-11-25 : 06:55:35

Thanks visakh16...
i got it before ur reply , even then thanks for ur kind reply.
Hats off to u and ur knowledge.
great men , keep showing others the way with the light of ur knowledge.
Keep in touch.

Every experience has something to learn. Go get it.
* Thanks and Regards *
genius_palli
Web Developer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 07:05:07

No worries
you're welcome

genius_palli
Starting Member

42 Posts

Posted - 2009-01-03 : 02:24:47

Hi everyone..
First of all A very Happy New Year 2009 !!! to all of u. Now back on the track...

Can anyone plz help regarding that can we execute an insert query within Common table expression.

Regards
genius_palli

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 06:43:07

quote:
Originally posted by genius_palli

Hi everyone..
First of all A very Happy New Year 2009 !!! to all of u. Now back on the track...

Can anyone plz help regarding that can we execute an insert query within Common table expression.

Regards
genius_palli

whats the purpose of using common table expression for insert? can you explain your scenario with some sample data?

genius_palli
Starting Member

42 Posts

Posted - 2009-01-04 : 23:24:52

Thanks Visakh..
I apologize any sample data may not be possible, but please let me try to explain the scenario so that you may suggest any different way to the problem.

SCENARIO :- I have a SELECT query in MS Access in which there is a reference to a stored procedure.
Within the stored procedure, a temporary table is created and some data in inserted into the temporary table using insert statement and then that temporary table is dropped.

PROBLEM :- I need to execute this query in SQL.

MY EFFORT :- I am trying to store the results of stored procedure into Common Table Expression and then further referring the Common Table Expression into the select query.

Hope this much is enough for you to suggest any alternative for me. Thanks in advance to your reply.

Regards
genius_palli

genius_palli
Starting Member

42 Posts

Posted - 2009-01-05 : 03:48:02

Any quick suggestions please on the above post. Thanks in advance.

Regards
genius_palli

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 04:24:44

i think you should be making stored procedure into table valued function so that you can directly take join with it just like you do with a table in your query and can dispense with the use of CTE.

genius_palli
Starting Member

42 Posts

Posted - 2009-01-05 : 05:23:41

you are right visakh, the stored procedure is used in the select query using a left join just like table. But there are no table valued functions or anything like that.

Within the stored procedure a temporary table is created using into #ICs , some data is inserted.
And this data is referenced into the select query. So could you please explain how can we do all that in SQL.
What i am thinking is to execute the code of stored procedure into Common table expression(CTE) and then further referencing the data within CTE .
I really got stucked in all that. plz help me to get out of that .
Feel free to ask for more details.

Regards
genius_palli

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-05 : 05:26:44

For a quick and dirty solution, create a linked server to "self", a loopback connector, and use the output from stored procedure in a OPENDATASOURCE function, which makes the output from the stored procedure look like a resultset.

E 12°55'05.63"
N 56°04'39.26"

genius_palli
Starting Member

42 Posts

Posted - 2009-01-05 : 05:40:48

Thanks Peso..
I want a quick solution but not the dirty one. Could you please explain bit more in detail. As i am new to development , i have never used functions.
Thanks in advance for your reply.

Regards
genius_palli

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 08:47:26

quote:
Originally posted by genius_palli

you are right visakh, the stored procedure is used in the select query using a left join just like table. But there are no table valued functions or anything like that.

Within the stored procedure a temporary table is created using into #ICs , some data is inserted.
And this data is referenced into the select query. So could you please explain how can we do all that in SQL.
What i am thinking is to execute the code of stored procedure into Common table expression(CTE) and then further referencing the data within CTE .
I really got stucked in all that. plz help me to get out of that .
Feel free to ask for more details.

Regards
genius_palli

why not use the insert query for temp table as a derived table and join with it (if its not too complex). or use temporary table in main query itself and take join with it.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources