Solved

Use SQL Statment in Store procedure as a table

Posted on 2013-01-10
1
299 Views
Last Modified: 2013-01-10
Hi Experts,

I have the below mentioned SQL statment, which I need to use in a store procedure as a table On the top to refer any where under the same SP. Please help


SELECT     TOP (100) PERCENT a.dt, a.dn, a.isholiday, a.holiday_name, b.EmpCode, b.EmpName,
                      CASE WHEN b.Direct = '1' THEN '1' ELSE '0' END AS Direct
FROM         dbo.AllDates a CROSS JOIN
             dbo.Emp_Info b
WHERE    b.CC=@CC and (b.Branch = 'KHI') and a.dt between @Dt and @Dt
and @Dt Between coalesce(JoiningDate,@Dt) and coalesce(RegsinedOn,@Dt)
ORDER BY a.dt, b.EmpCode
0
Comment
Question by:Mehram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38762473
as it has parameters, you cannot refer to it as a table Unless you store the results in a table.
it may be a table variable:

declare @t table ( dt datetime, dn varchar(10) ... etc .. )
insert into @t select top (100) percent ... etc

and from there you can then use the @t table:

select * from @t

etc ..
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 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