November 15, 2010 at 3:01 pm
hi..
Doing this
With MyTable AS (SELECT <fields> FROM <table>)
IF @var1 = 1
BEGIN
SELECT <field> FROM myTable WHERE <Field> = @var2
END
ELSE IF @var1 = 2
BEGIN
SELECT <field> FROM myTable WHERE <Field> = @var3
END
ELSE IF @var1 = 3
BEGIN
SELECT <field> FROM myTable WHERE <Field> = @var4
END
Running TSQL above gives errors as the WITH table is not within the BEGIN and END of the queries.
I get round this buy using a #temp table, but it seams this is not wise on a select statement that is very active ( ie behind a blog), what else can I do ???;-)
Thanks
November 15, 2010 at 3:05 pm
Digs (11/15/2010)
I get round this buy using a #temp table, but it seams this is not wise on a select statement that is very active ( ie behind a blog), what else can I do ???;-)
Use a view. Define the cte for each if statement. Use a separate proc for each @var type call. Use Dynamic SQL. There are many other approaches, but try those first. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 3:12 pm
A view, I understood a view was a no no !
Question how can I use a procedure to populate a SELECT ??
November 15, 2010 at 3:17 pm
Digs (11/15/2010)
A view, I understood a view was a no no !Question how can I use a procedure to populate a SELECT ??
Views are not the anti-god. They are just highly annoying (to me) and can be occassionally unpredictable until you understand why it decided to go off the deep end on you. I mostly don't like them because they tend to obfuscate code unnecessarily. If you're calling the same SELECT statement 40-50 times, sure, use a view. Every subselect doesn't need a view.
Ummm... /RANT. Sorry.
INSERT INTO #tmp EXEC procname can allow you to drop a proc's results into a temp table.
Call that #tmp into another table if necessary.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2010 at 3:32 pm
Thanks, I ended up creating a function that return a table...all seams good !:-)
November 15, 2010 at 4:28 pm
If you didn't do the function right, it can be worse than the view you don't want to use...
Want to post what you did and let us look at it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 15, 2010 at 4:48 pm
I have under a 1000 blos posts, so there is not a lot of data to worry about...
but here you go...
USE [DB785342]
GO
/****** Object: UserDefinedFunction [dbo].[fnBlogPosts] Script Date: 11/16/2010 12:47:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnBlogPosts]
(
@BlogID INT,
@Page INT
)
RETURNS TABLE
AS
RETURN
WITH tblBlogReadType AS (SELECT A.[Page]
,C.[Category] AS 'PostCategory'
,A.[Title]
,SUBSTRING(A.[Notes],1,CHARINDEX('[READMORE]',A.[Notes])+LEN('[READMORE]')-1) AS 'Notes'
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,A.[BlogTransID]
,A.[BlogUserID]
,A.[HeaderImage]
,D.[Width] AS 'HeaderImageWidth'
,D.[Height] AS 'HeaderImageHeight'
,B.Name AS 'Author'
,(YEAR(A.[CreatedOn])*100)+MONTH(A.[CreatedOn]) AS 'YYYYMM'
,1 AS 'BlogReadType'
,A.PageOrder
FROM [dbo].[DIM_Blog] A
INNER JOIN dbo.[FACT_Blog_Users] B
ON A.BlogUserID = B.BlogUserID AND A.BlogID = B.BlogID
INNER JOIN dbo.FACT_Blog_PostCategory C
ON A.PostCategory = C.BlogCatID AND A.BlogID = C.BlogID
INNER JOIN FACT_Blog_HeaderImgSize D
ON A.HeaderImageSizeID = D.HeaderImgSizeID
WHERE A.[BlogID] = @BlogID
AND A.Deleted = 0
AND A.Show = 'Yes'
AND A.Page = @Page
AND CHARINDEX('[READMORE]',A.[Notes])>0
AND A.[HeaderImage]<>'NoImage'
UNION ALL
SELECT A.[Page]
,C.[Category] AS 'PostCategory'
,A.[Title]
,SUBSTRING(A.[Notes],1,CHARINDEX('[READMORE]',A.[Notes])+LEN('[READMORE]')-1) AS 'Notes'
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,A.[BlogTransID]
,A.[BlogUserID]
,A.[HeaderImage]
,D.[Width] AS 'HeaderImageWidth'
,D.[Height] AS 'HeaderImageHeight'
,B.Name AS 'Author'
,(YEAR(A.[CreatedOn])*100)+MONTH(A.[CreatedOn]) AS 'YYYYMM'
,2 AS 'BlogReadType'
,A.PageOrder
FROM [dbo].[DIM_Blog] A
INNER JOIN dbo.[FACT_Blog_Users] B
ON A.BlogUserID = B.BlogUserID AND A.BlogID = B.BlogID
INNER JOIN dbo.FACT_Blog_PostCategory C
ON A.PostCategory = C.BlogCatID AND A.BlogID = C.BlogID
INNER JOIN FACT_Blog_HeaderImgSize D
ON A.HeaderImageSizeID = D.HeaderImgSizeID
WHERE A.[BlogID] = @BlogID
AND A.Deleted = 0
AND A.Show = 'Yes'
AND A.Page = @Page
AND CHARINDEX('[READMORE]',A.[Notes])>0
AND A.[HeaderImage]='NoImage'
UNION ALL
SELECT A.[Page]
,C.[Category] AS 'PostCategory'
,A.[Title]
,A.[Notes]
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,A.[BlogTransID]
,A.[BlogUserID]
,A.[HeaderImage]
,D.[Width] AS 'HeaderImageWidth'
,D.[Height] AS 'HeaderImageHeight'
,B.Name AS 'Author'
,(YEAR(A.[CreatedOn])*100)+MONTH(A.[CreatedOn]) AS 'YYYYMM'
,0 AS 'BlogReadType'
,A.PageOrder
FROM [dbo].[DIM_Blog] A
INNER JOIN dbo.[FACT_Blog_Users] B
ON A.BlogUserID = B.BlogUserID AND A.BlogID = B.BlogID
INNER JOIN dbo.FACT_Blog_PostCategory C
ON A.PostCategory = C.BlogCatID AND A.BlogID = C.BlogID
INNER JOIN FACT_Blog_HeaderImgSize D
ON A.HeaderImageSizeID = D.HeaderImgSizeID
WHERE A.[BlogID] = @BlogID
AND A.Deleted = 0
AND A.Show = 'Yes'
AND A.Page = @Page
AND CHARINDEX('[READMORE]',A.[Notes])=0
AND A.[HeaderImage]= 'NoImage')
SELECT ROW_NUMBER() OVER(ORDER BY [BlogTransID] DESC) AS 'RowNum'
,A.[Page]
,A.[PostCategory]
,A.[Title]
,A.[Notes]
,A.[Color]
,A.[CreatedOn]
,A.[LastUpdate]
,A.[BlogTransID]
,A.[BlogUserID]
,A.[Author]
,A.[HeaderImage]
,A.[HeaderImageWidth]
,A.[HeaderImageHeight]
,A.[YYYYMM]
,A.[BlogReadType]
,A.PageOrder
FROM tblBlogReadType A
November 16, 2010 at 6:53 am
Digs (11/15/2010)
A view, I understood a view was a no no !
May I ask why why? 😀
Digs (11/15/2010)
Question how can I use a procedure to populate a SELECT ??
... not only a select
CREATE TABLE [#dba_xp_fixeddrives] (
[drive] [varchar] (08) NULL ,
[mbfree] [varchar] (18) NULL
) ON [PRIMARY]
insert into dbo.#dba_xp_fixeddrives exec master..xp_fixeddrives
select * from dbo.#dba_xp_fixeddrives
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply