Stored procedure - quering multiple products
-
Wednesday, June 13, 2012 12:13 PM
Hi All
I'm busy altering a stored procedure that queries the three different products and then creates a report for the product that you selected
the part of the stored proc where I am having an issue are in the following part
IF @product='SCCM'
set @Fixview = 'SMS2003'
else set @Fixview = ''
--this caters for wsus
what I would like to do is to bring in SCCM2012 as well so I thought easy enough iwould just do the following
IF @product='SCCM'
set @Fixview = 'SCCM2012' -- The moment I add this to the sql -it states that there is an issue at the else statement
set @Fixview = 'SMS2003'
else set @Fixview = ''
my question is how will I cater for an extra product ?
I hope this makes sense - thx for the help
All Replies
-
Wednesday, June 13, 2012 12:24 PMModerator
I think in your case you just need 3 IF statements, e.g.
IF @Product = 'SCCM'
SET @FixView = 'SMS2003'
IF @Product = 'SCCM2012'
SET @FixView = 'SMS2008'
But if you need to have more than 1 line in the IF block, you need to put it in BEGIN END block, e.g.
IF SomeCondition
BEGIN
statement1
statement2
END
You can use BEGIN/END even for 1 line statement also.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
Wednesday, June 13, 2012 12:25 PMAnswerer
set @Fixview = 'SCCM2012,SMS2003' ????
http://www.sommarskog.se/arrays-in-sql-2008.html
Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/
- Proposed As Answer by Syed Qazafi AnjumMicrosoft Community Contributor Sunday, June 17, 2012 4:43 AM
-
Friday, June 15, 2012 9:01 AM
Hi All
ok Ive done so far what Naomi suggested - it seem to get rid of the problem at "else" - just picking up other issues now
the basics are we have create a standard report for SMS2003 SCCM2007 WSUS and we are now adding SCCM 2012 - This is where the fun is starting
I will keep you posted - I just want to see if I can sort out the new issues in this stored proc
-
Friday, June 15, 2012 9:11 AM
can somebody please do a sanity check below please
im getting funny issues -I have created a temptable called ciflags for sccm2012 because there is a huge difference where I collect info from between the sccm2007 db and the SCCM2012 db - it is connecting to the correct server and db but it is looking for the wrong view "vsms_g_system_patchstatusex" (this is in sccm2007 not in 2012) what am I doing wrong
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ArticledID'.
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "172.25.110.11" does not contain the table ""CM_CEN"."dbo"."vSMS_G_System_PatchstatusEx"". The table either does not exist or the current user does not have permissions on that table.
USE [ISMC_Dashboard]
GO
/****** Object: StoredProcedure [dbo].[zspr_Reporting_Approved_Patch_Detail-mike5] Script Date: 06/15/2012 11:02:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[zspr_Reporting_Approved_Patch_Detail-mike5]
@client varchar(40),
@service varchar(40)AS
-- =============================================
-- Author: Louw jacobs
-- Create date: 7 Feb 2011
-- Description: Detail of all approved patches last 30 days
-- =============================================
BEGINSET NOCOUNT ON
declare @ipaddress varchar(255),
@database varchar(100),
@Product varchar(10),
@Fixview varchar(10),
@IDCheck varchar(40),
@sqlquery nVarchar(4000),
@classification varchar(20),
@whereClause nVarchar(1000),
@whereClause2 nVarchar(1000)
select @ipaddress = IP,
@database = [Database],
@Product = Product,
@classification = Classification,
@whereClause = WhereClause,
@whereClause2 = WhereClause2
from dbo.Client
where Client = @client
--and Product = 'SCCM'
and Service like @service
IF @product='SCCM'
set @Fixview = 'SMS'
if @Product ='SCCM2012'
set @Fixview = 'SCCM2012'
else set @Fixview = ''
If @Client not like 'ETC%'
set @IDCheck = 'and AUS.ID like ''MS%'''
else set @IDCheck = ''
-- Build temp table for superseded test --
IF OBJECT_ID(N'tempdb..#Patchflags', N'U') IS NOT NULL
Drop Table #Patchflags
Create Table #Patchflags
(
UpdateID uniqueidentifier,
IsExpired Int,
IsSuperseded Int,
Severity Int
)
--varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--SCCM 2012 START
Create Table #CIflags
(
CI_ID int,
BulletinID varchar(20),
ArticleID varchar(20),
IsExpired Int,
IsSuperseded Int,
Severity Int,
IsDeployed Int,
DateRevised DateTime,
EffectiveDate DateTime,
Title varchar(250),
Xml varchar(50)
)
set @sqlquery= 'Insert #CIflags Select CI_ID,BulletinID,ArticleID,IsExpired,IsSuperseded,Severity,IsDeployed,
DateRevised,EffectiveDate,Title,Xml
FROM
OPENQUERY(['+ @ipaddress +'],' +''' SELECT
CI_ID,BulletinID,ArticleID ,IsExpired,IsSuperseded,Severity,IsDeployed,DateRevised,EffectiveDate,
Title,left(Cast(SDMPackageDigest as Varchar(max)),20) as Xml
FROM
[' + @database + '].[dbo].[v_UpdateInfo]
WHERE ISDEPLOYED = 1
''
)'
Exec sp_executesql @sqlquery
IF @product='SCCM2012'
begin
set @sqlquery = 'Insert #CIFLAGS Select ArticledID, IsExpired,IsSuperseded,Severity
FROM
OPENQUERY(['+ @ipaddress +'],' +''' SELECT
CI_ID,BulletinID,ArticleID ,IsExpired,IsSuperseded,Severity,IsDeployed,DateRevised,EffectiveDate,
Title,left(Cast(SDMPackageDigest as Varchar(max)),20) as Xml
FROM
[' + @database + '].[dbo].[v_UpdateInfo]
WHERE ISDEPLOYED = 1
''
)'
end
--SCCM 2012 FINISHED
IF @product='SCCM'
begin
set @sqlquery= 'Insert #Patchflags Select UpdateID, IsExpired,IsSuperseded,Severity
FROM
OPENQUERY(['+ @ipaddress +'],' +''' SELECT
CI_UniqueID UpdateID,IsExpired,IsSuperseded,Severity,
left(Cast(SDMPackageDigest as Varchar(max)),20) as Xml
FROM
[' + @database + '].[dbo].[vSMS_SoftwareUpdate]''
)'
end
if @Product = 'SMS'
begin
set @sqlquery= 'Insert #Patchflags Select distinct UpdateID00 UpdateID,0,0,Severity00 Severity
FROM
[' + @ipaddress + '].[' + @database + '].dbo.Software_Updates_Extended_DATA
'
end
Exec sp_executesql @sqlquery
create table #Patchdetail
(
ID varchar(20),
Qnumbers int,
Title varchar(255),
TimeAuthorized DateTime,
DateRevised DateTime,
Severity varchar(20)
)
if @Product = 'WSUS'
Begin
set @sqlquery= 'Insert #Patchdetail select Distinct SecurityBulletin, Knowledgebasearticle, DefaultTitle, ArrivalDate,CreationDate, msrcSeverity
from [' + @ipaddress + '].[' + @database + '].Public_Views.vUpdateInstallationInfo_db uii (nolock)
Inner Join [' + @ipaddress + '].[' + @database + '].Public_Views.vupdate u (nolock) on u.updateID = UII.updateID
Inner Join [' + @ipaddress + '].[' + @database + '].Public_Views.zComputerinfo ci (nolock) on Ci.ComputerTargetID = UII.ComputerTargetID
where ArrivalDate > GETDATE() - 30
' + @whereClause +
' ORDER BY SecurityBulletin desc'
End
Else
Begin
set @sqlquery= 'Insert #Patchdetail select distinct AUS.ID,AUS.Qnumbers,AUS.Title,TimeAuthorized,DateRevised,Severity =
Case PF.Severity
when 10 then ''Critical''
when 8 then ''Serious''
when 6 then ''important''
else '' '' end
FROM [' + @ipaddress + '].[' + @database + '].dbo.v_R_System RS
INNER JOIN [' + @ipaddress + '].[' + @database + '].dbo.vSMS_G_System_PatchstatusEx PS ON RS.ResourceID = PS.ResourceID
INNER JOIN [' + @ipaddress + '].[' + @database + '].dbo.v'+@Fixview+'_ApplicableUpdatesSummaryEx AUS ON AUS.UpdateID = PS.UpdateID
INNER JOIN #Patchflags PF on PS.UniqueUpdateID = PF.UpdateID
Where
AUS.TimeAuthorized is not NULL and daterevised > GETDATE() - 30
' + @IDCheck + '
and Operating_System_Name_and0 like ''%' + @classification+ '%'' ' + @whereClause + '
ORDER BY AUS.ID desc'End
Exec sp_executesql @sqlquerySelect * FROM #Patchdetail order by 1 desc
END
-
Sunday, June 17, 2012 4:32 AMModerator
This is a long code, so I think I'll point only one issue:
IF @product='SCCM' set @Fixview = 'SMS' if @Product ='SCCM2012' set @Fixview = 'SCCM2012' else set @Fixview = ''
I suggest this re-write:
set @Fixview = '' IF @product='SCCM' set @Fixview = 'SMS' if @Product ='SCCM2012' set @Fixview = 'SCCM2012'I also suggest to use print @sqlquery instead of using sp_executeSQL.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Edited by Naomi NMicrosoft Community Contributor, Moderator Sunday, June 17, 2012 4:45 AM
-
Sunday, June 17, 2012 9:29 PMPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html
>> I'm busy altering a stored procedure that queries the three different products and then creates a report for the product that you selected <<
Now we have to read your mind!
>> the part of the stored proc where I am having an issue are in the following part <<
@product is not a valid data elment name; it needs an attribute property like product_name, product_category, etc which you were too rude to tell us.
IF @product = 'SCCM'
SET @fixview = 'SMS2003'
ELSE SET @fixview = '';
NO SQL Programer would write that procedural control flow. We use
SET @fixview
= CASE WHEN @product_name = 'SCCM'
THEN 'SMS2003'
ELSE '' END;
But there is a good chance we would replace the @fixview with the CASE expression and not bother with a local variable at all. Thast is declarative programming.
what I would like to do is to bring in SCCM2012 as well so I thought easy enough I would just do the following
IF @product =' SCCM'
SET @fixview = 'SCCM2012'
SET @fixview = 'SMS2003'
ELSE SET @fixview = '';
This makes no sense at all! How did you expect your poorly named @fixview to hold two different values at the same time?--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
-
Sunday, June 17, 2012 10:13 PM
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.I recommend inexperienced users to ignore Joe Celko. "Minimal politeness" is his trademark - there is certainly not much of it.
And of course, in this forum, you should speak the local dialect, i.e. T-SQL and standard SQL is only useful when it aligns with T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Monday, June 18, 2012 11:10 AM
Hi All
Thank you for all the suggestions so far, and I regard all coments as postive critisicm and no offense has been taken. I am complete novice when it comes to SQL what I learned so far is through trial and error. So you can call your typical back yard Mechanic SQL writer
.png)
