RE: User Defined Function Help
From: Travis Falls (travis.falls_at_htsco.com)
Date: 11/04/04
- Next message: Michael Hardy: "Re: CREATE TABLE USING SQL"
- Previous message: Adam Machanic: "Re: Encrypted SQL code"
- In reply to: Travis Falls: "RE: User Defined Function Help"
- Next in thread: Aaron [SQL Server MVP]: "Re: User Defined Function Help"
- Reply: Aaron [SQL Server MVP]: "Re: User Defined Function Help"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 04 Nov 2004 08:00:39 -0800
I have to run a sp against the results of that function that will be
very similar to this:
CREATE PROCEDURE [dbo].[PROC_getLogs]
@count varchar(5)='20',
@node varchar(50)=null,
@server varchar(50)=null,
@service varchar(50)=null,
@subservice varchar(50)=null,
@jobid varchar(10)=null,
@type varchar(1)=null,
@messid varchar(10)=null,
@search varchar(1000)=null,
@startDateTime datetime = null,
@endDateTime datetime = null
AS
DECLARE @queryString varchar(1000)
DECLARE @selectFromList varchar(1000)
DECLARE @LogView varchar(1000)
DECLARE @JobIDView varchar(1000)
DECLARE @NodeView varchar(1000)
DECLARE @ServerView varchar(1000)
DECLARE @ServiceView varchar(1000)
DECLARE @SubServiceView varchar(1000)
DECLARE @TypeView varchar(1000)
DECLARE @MessIDView varchar(1000)
SET DATEFORMAT mdy
SET NOCOUNT ON
SET @queryString = ''
SET @selectFromList = ''
SET @node = RTRIM(@node)
SET @server = RTRIM(@server)
SET @service = RTRIM(@service)
SET @subservice = RTRIM(@subservice)
SET @type = RTRIM(@type)
SET @jobid = RTRIM(@jobid)
SET @messid = RTRIM(@messid)
if (RTRIM(@count) ='')
BEGIN
SET @count = '20'
END
if (@node is not null)and(RTRIM(@node) <> '')
BEGIN
Set @queryString=@queryString + ' AND NODE =' + "'" + @node + "'"
Set @selectFromList =@selectFromList + ',NODE'
END
if (@server is not null)and(RTRIM(@server) <>'')
BEGIN
Set @queryString=@queryString+' AND SERVER = '+ "'" + @server +"'"
Set @selectFromList =@selectFromList + ',SERVER'
END
if (@service is not null)and(RTRIM(@service) <>'')
BEGIN
Set @queryString=@queryString+' AND SERVICE =' +"'"+ @service +"'"
Set @selectFromList =@selectFromList + ',Service'
END
if (@subservice is not null)and(RTRIM(@subservice) <>'')
BEGIN
Set @queryString=@queryString+' AND SUBSERVICE ='+ "'"+@subservice
+"'"
Set @selectFromList =@selectFromList + ',SubService'
END
if (@jobid is not null)and(RTRIM(@jobid) <>'')
BEGIN
Set @queryString=@queryString+ 'AND JOB_ID ='+"'"+ @jobid +"'"
Set @selectFromList =@selectFromList + ',JOB_ID'
END
if (@type is not null)and(RTRIM(@type) <>'')
BEGIN
Set @queryString=@queryString+' AND TYPE ='+"'"+ @type+"'"
Set @selectFromList =@selectFromList + ',TYPE'
END
if (@messid is not null)and(RTRIM(@messid) <>'')
BEGIN
Set @queryString=@queryString+' AND MESSAGE_ID ='+"'"+ @messid +"'"
Set @selectFromList =@selectFromList + ',MESSAGE_ID'
END
if (@search is not null)and(RTRIM(@search) <>'')
BEGIN
Set @queryString=@queryString+' AND MESSAGE LIKE '+"'%"+ @search +"%'"
END
if ((@startDateTime <> null)AND (@endDateTime <> null))
BEGIN
Set @queryString=@queryString+' AND DATETIME BETWEEN '+ ''''+
CONVERT(VARCHAR,@startDateTime) + '''' + ' AND ' + '''' +
CONVERT(VARCHAR,@endDateTime) +''''
Set @selectFromList =@selectFromList + ',DateTime'
END
SELECT @LogView = 'SELECT Top ' + @count + ' LTRIM(RTRIM(NODE)) as Node,
LTRIM(RTRIM(SERVER)) as Server,LTRIM(RTRIM(SERVICE)) as Service,
LTRIM(RTRIM(SUBSERVICE)) as SubService, LTRIM(RTRIM(JOB_ID)) as RaftID,
LTRIM(RTRIM(TYPE)) as Type, LTRIM(RTRIM(MESSAGE_ID)) as MessageID,
MESSAGE as Message, DATETIME as DateTime FROM MESSAGES WHERE 1=1' +
@queryString + ' Order By DateTime Desc'
SELECT @JobIDView = 'SELECT DISTINCT LTRIM(RTRIM(JOB_ID)) as RaftID FROM
MESSAGES WHERE 1=1 AND JOB_ID <>'''' ' + @queryString + ' Order By
RaftID ASC'
SELECT @NodeView = 'SELECT DISTINCT LTRIM(RTRIM(NODE)) as Node FROM
MESSAGES WHERE 1=1 AND NODE <>'''' ' + @queryString + ' Order By Node
ASC'
SELECT @ServerView = 'SELECT DISTINCT LTRIM(RTRIM(SERVER)) as Server
FROM MESSAGES WHERE 1=1 AND SERVER <> '''' ' + @queryString + ' Order By
Server ASC'
SELECT @TypeView = 'SELECT DISTINCT LTRIM(RTRIM(TYPE)) as Type FROM
MESSAGES WHERE 1=1 AND TYPE <>'''' ' + @queryString + ' Order By Type
ASC'
SELECT @ServiceView = 'SELECT DISTINCT LTRIM(RTRIM(SERVICE)) as Service
FROM MESSAGES WHERE 1=1 AND SERVICE <> '''' ' + @queryString + ' Order
By Service ASC'
SELECT @SubServiceView = 'SELECT DISTINCT LTRIM(RTRIM(SUBSERVICE)) as
SubService FROM MESSAGES WHERE 1=1 AND SUBSERVICE <> '''' ' +
@queryString + ' Order By SubService ASC'
SELECT @MessIDView = 'SELECT DISTINCT LTRIM(RTRIM(MESSAGE_ID)) as
MessageID FROM MESSAGES WHERE 1=1 AND MESSAGE_ID <> '''' ' +
@queryString + ' Order By MessageID ASC'
EXEC (@LogView)
EXEC (@JobIDView)
EXEC (@NodeView)
EXEC (@ServerView)
EXEC (@ServiceView)
EXEC (@SubServiceView)
EXEC (@TypeView)
EXEC (@MessIDView)
GO
I then get my menu items, and results all at once in my C# code. The
database, which I didn't build makes a new table every month. That is
why I want to UNION them all. I just think it will be to complicated to
try to do all this and all the union stuff in one sp.
- Rugby
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Michael Hardy: "Re: CREATE TABLE USING SQL"
- Previous message: Adam Machanic: "Re: Encrypted SQL code"
- In reply to: Travis Falls: "RE: User Defined Function Help"
- Next in thread: Aaron [SQL Server MVP]: "Re: User Defined Function Help"
- Reply: Aaron [SQL Server MVP]: "Re: User Defined Function Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
