Need a Stored Procedure Urgently.

I have Five tables and i want to get result from these five tables providing some checks.

Site
----------------------------
SiteID int,
SiteTypeID int,
PowerTypeID int,
LocationID int,
SiteCode nvarchar(500)

Sites_SiteType
-----------------------------
ID int,
Type nvarchar(500)

Sites_PowerType
-----------------------------
ID int,
Type nvarchar(500)

SiteTypeRenewal
-----------------------------
RenewalID int,
SiteTypeId int,
SiteID


PowerTypeRenewal
-----------------------------
RenewalID int,
PowerTypeId int,
SiteID


Site Data
----------------
SiteID SiteTypeID PowerTypeID LocationID SiteCode
1 10 3 100 xxx
2 20 1 200 yyy
3 30 2 300 zzz
4 30 1 100 xxx


Sites_siteType Data
----------------------------
ID Type
10 RoofTop
20 Transformer
30 GreenField


SiteTypeRenewal
----------------------------
RenewalID SiteTypeID SiteID
1 20 1
2 30 2
3 10 3
4 10 2
5 20 3

Sites_PowerType Table Data
----------------------------
ID Type
1 Generator
2 T.VStation
3 Transformer


PowerTypeRenewal
----------------------------
RenewalID PowerTypeID SiteID
1 2 1
2 3 2
3 1 3
4 1 2
5 2 3

herei want to check in the renewals(SiteTypeRenewal and PowerTypeRenewal)tables if there are records for a particular (SiteID) then extractmaximum(last) record based on SiteID and display Type of Site_siteTypeand Site_PowerType by inner joining from renewal table. here ifSiteID=3, it should give me LocationID=300,SiteCode=zzzSiteType=Transformer and PowerType=T.VStation.
else check in theSite Table whether that SiteID is available or not if available extractthat record joining Sites_SiteType and Site_PowerType and display it exhere for this if SiteID=4, it should give meLocationID=100,SiteCode=xxx SiteType=GreenField and PowerType=Generator.

my final result looks like this...

for SiteID=3
SiteID LocationID SiteCode SiteType PowerType

3 300 zzz Transformer T.VStation

for SiteID=4 which is not available in renewals tables

SiteID LocationID SiteCode SiteType PowerType

4 100 xxx GreenField Generator




i have written procedures for single value extraction.. just for understanding purpose i giving this.

Alter procedure Proc_Name2
@siteid int
as
begin
declare @sid int


if exists(select SiteID from Site_Renewal_SiteType where SiteID=@siteid)
select top 1 SSRT.SiteTypeRenewalID,Site_SiteTypes.Type from Site_SiteTypes where SSRT.SiteID=@siteid INNER JOIN
Site_Renewal_SiteType SSRT ON Site_SiteTypes.ID = SSRT.SiteTypeID INNER JOIN
New_Site ON SSRT.SiteID = New_Site.SiteID
order by SSRT.SiteTypeRenewalID desc
else if exists(select SiteID from New_Site where SiteID=@siteid)
select SiteID from New_Site
else
select 'N/A'
end
go





Alter procedure Proc_Name3
@siteid int
as
begin
if exists(select SiteID from Site_Renewal_PowerType where SiteID=@siteid)
select top 1 SSPT.PowerTypeRenewalID,Site_PowerTypes.Type from Site_PowerType where SSPT.SiteID=@siteid INNER JOIN
Site_Renewal_PowerType SSPT ON Site_PowerType.ID = SSPT.PowerTypeID INNER JOIN
New_Site ON SSPT.SiteID = New_Site.SiteID
order by SSPT.PowerTypeRenewalID
else if exists(select SiteID from New_Site where SiteID=@siteid)
select SiteID from New_Site
else
select 'N/A'
end
go


from these two procedures i can get single column.

but i want all 5 Columns which is present in Site Table should display based on renewals tables.

please if any know this.. kindly reply.

First option is to do a UNION ALL on one query that returns results from SiteTypeRenewal based on your given criteria, and a second query that returns results from PowerTypeRenewal again based on your given criteria.
Second option is to do a full join on SiteTypeRenewal and PowerTypeRenewal (on SiteID) and outer joins on all tables from which you will need the lookup values. Then use CASE statements for the column expressions, comparing the renewal details from both fully joined tables, and returning the column from the appropriate table.

Hello Adriaan.
i am unable do this. Since there are two procedures i am unable to join these two procedures.
Please give the example code to write this. so that i can try for it.
Hope to get a reply from u.
Thanks.

UNION [ALL] is a keyword that you can put between two queries. This will combine the results of the two queries in one single resultset.
The only requirements are that the number of columns are the same, that the columns are in the same order, and that the data types of each set of columns match.
So you set up one query for the results from SiteTypeRenewal, with the criteria that would return the relevant results from this table and from any joined tables in this first query.
Next you set up another query for the results from PowerTypeRenewal, again with the criteria that would return the relevant results from this table and from any joined tables in this second query.
Just put UNION ALL between the two queries in your script. It doesn't matter that one of the queries doesn't return any results - the other query will.

I have written here like this... But it is giving me error.. Incorrect syntax near the keyword 'if'.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[Proc_Name2]
@siteid int
as
begin
declare @sid int

if exists(select SiteID from Site_Renewal_SiteType where SiteID=@siteid)
select top 1 SiteTypeRenewalID,SiteTypeID,SiteID from Site_Renewal_SiteType where SiteID=@siteid order by SiteTypeRenewalID desc
else if exists(select SiteID from New_Site where SiteID=@siteid)
select SiteID from New_Site
else
select 'N/A' as 'SiteType'

Union All

if exists(select SiteID from Site_Renewal_PowerType where SiteID=@siteid)
select top 1 PowerTypeRenewalID,PowerTypeID,SiteID from Site_Renewal_PowerType where SiteID=@siteid order by PowerTypeRenewalID desc
else if exists(select SiteID from New_Site where SiteID=@siteid)
select SiteID from New_Site
else
select 'N/A' as 'PowerType'


end
Please reply..
i want something
Create Procedure RenewalsBased
@SiteID int
as
Select SiteID,LocationID,SiteCode,
(if exists(select SiteID from Site_Renewal_SiteType where SiteID=@SiteID)
Select Site_SiteTypes.Type
FROM Site_SiteTypes INNER JOIN
Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
(SELECT MAX(SiteTypeRenewalID) AS Expr1
FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 where Site_Renewal_SiteType_1.SiteID = @SiteID))
else
SELECT Site_SiteTypes.Type
FROM New_Site INNER JOIN
Site_SiteTypes ON New_Site.SiteType = Site_SiteTypes.ID
WHERE (New_Site.SiteID = @SiteID)),
(if exists(select SiteID from Site_Renewal_PowerType where SiteID=@SiteID)
Select Site_PowerTypes.Type
FROM Site_PowerTypesINNER JOIN
Site_Renewal_PowerTypesON Site_PowerTypes.ID = Site_Renewal_PowerTypes.PowerTypeID
WHERE (Site_Renewal_PowerTypes.PowerTypeRenewalID =
(SELECT MAX(PowerTypeRenewalID ) AS Expr1
FROM Site_Renewal_PowerTypes AS Site_Renewal_PowerTypes_1 where Site_Renewal_PowerTypes_1.SiteID =@SiteID))
else
SELECT Site_PowerTypes.Type
FROM New_Site INNER JOIN
Site_PowerTypesON New_Site.PowerTypes= Site_SiteTypes.ID
WHERE (New_Site.SiteID = @SiteID)) from New_Site where SiteID=@SiteID




but it is giving error near if condition saying that error near if.

Don't use control-of-flow syntax (IF) here.
Write a straightforward query that returns the results from SiteTypeRenewal with its joined tables. Include a WHERE clause that ensures that only the proper results are returned.
Then write another straightforward query that returns the results from PowerTypeRenewal with its joined tables. Again include a WHERE clause that ensures that only the proper results are returned.
Then put UNION ALL between those two queries.

Here if i dont use control of flow IF then how can i check whether there is a record in renewals tables??? If there is no record in renewals table then i want to extract it from Site Table... So If is necessary there i think.

Use the WHERE NOT EXISTS (correlated subquery) syntax.

If i use Where Not exist it gives only records which are not available in renewals tables... but if there are records in Renewals table then i want that record based on max and siteID condition.
Have a look Here...
(if exists(select SiteID from Site_Renewal_SiteType where SiteID=@SiteID)
if there is record in Renwals table I am extracting it from renewals based on my criteria
Select Site_SiteTypes.Type
FROM Site_SiteTypes INNER JOIN
Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
(SELECT MAX(SiteTypeRenewalID) AS Expr1
FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 whereSite_Renewal_SiteType_1.SiteID = @SiteID))
Now Here Else is for Retriving record from Site Table...i.e, if there is no record in Renewals then iam extracting it from Site Table. I am extracting in my both conditions if available in renewals extract else extract from site table.
SELECT Site_SiteTypes.Type
FROM New_Site INNER JOIN
Site_SiteTypes ON New_Site.SiteType = Site_SiteTypes.ID
WHERE (New_Site.SiteID = @SiteID))
I think u got my point now. And also if i use Union all i will get data in the form of rows not like columns....
i want
SiteType PowerType
Greenfield Generator
union all gives like this
No Columns
GreenField
Generator
which i dont want.

I've already outlined what you need to do. The whole point is that you need to stop thinking in IF ... ELSE ... logic.
To get started, write a simple query that reads the results from Site_SiteTypes and any tables you want to join on it, and add the WHERE clause for SiteID.
To this simple query, at the end of the WHERE clause, add
AND NOT EXISTS (subquery)
... this subquery is one that tries to find rows in the other source table, given the same criteria.
The NOT EXISTS keyword makes sure that it will only show rows from this table if there is no match in that table.
Now repeat this for the other source table, with a NOT EXISTS subquery that tries to find rows in Site_SiteTypes.
Put these two queries together, one after the other, and stick UNION ALL between them.

Dear,
According to u i wrote this query.
It is giving output only if there is no record in Renewals table and if there is any record then it is not showing that record.
Alter procedure proced
@SiteID int
as
Begin
SELECT Site_SiteTypes.Type as SiteType
FROM New_Site INNER JOIN
Site_SiteTypes ON New_Site.SiteType= Site_SiteTypes.ID
where SiteID=@SiteID and Not Exists(Select Site_SiteTypes.Type as SiteType
FROM Site_SiteTypes INNER JOIN
Site_Renewal_SiteType ON Site_SiteTypes.ID = Site_Renewal_SiteType.SiteTypeID INNER JOIN
New_Site ON Site_Renewal_SiteType.SiteID = New_Site.SiteID
WHERE (Site_Renewal_SiteType.SiteTypeRenewalID =
(SELECT MAX(SiteTypeRenewalID) AS Expr1
FROM Site_Renewal_SiteType AS Site_Renewal_SiteType_1 where Site_Renewal_SiteType_1.SiteID =@SiteID)))

Well, it's starting to look promising ... but I would have expected the NOT EXISTS subquery to refer to the Power*** tables, not the Site*** tables themselves.
Also I would have expected to see more columns for the resultset.
And this would be the FIRST of TWO queries. The second query would read be exactly like this one, but with the main part reading columns from the Power*** tables, with a NOT EXISTS subquery referring to the Site*** tables.
So you would have those two rather complicated queries, and you would simply stack the resultsets by adding UNION ALL between the two queries.

Just as an example:SELECT a.Site, a.AID FROM tableA a WHERE NOT EXISTS (SELECT b.BID FROM tableB b WHERE b.BID = a.AID)
UNION ALL
SELECT b.Site, b.BID FROM tableB B WHERE NOT EXISTS (SELECT a.AID FROM tableA a WHERE a.AID = b.BID)

/* create procedure*/create procedure site_info@siteid int
as
beginselect s.siteid,s.locationid,s.sitecode,s.SiteTypeID
,case when sst.[type]is null then st.[type] else sst.[type] end as sitetype,s.PowerTypeID
,case when spt.[type] is null then pt.[type] else spt.[type] end as powertypefrom site as s
left join (select siteid,max(sitetypeid)as sitetypeidfrom SiteTypeRenewal
group by siteid)as sr on s.siteid = sr.siteidleft join Sites_SiteType as sst on sr.sitetypeid = sst.id
left join (select siteid,max(powertypeid)as powertypeidfrom PowerTypeRenewal
group by siteid)as pr on s.siteid = pr.siteidleft join Sites_PowerType as spt on spt.id = pr.powertypeid
LEFT JOIN Sites_SiteType as st on s.sitetypeid = st.idleft join Sites_PowerType as pt on pt.id = s.powertypeid
where s.siteid = case when @siteid is null then s.siteid else @siteid endend/*execute*/exec
site_info 3exec
site_info nullexec
site_info 4