Converting a stored procedure into a View

Question

Converting a stored procedure into a View

Mark Harley

SSCrazy

Points: 2259
More actions
June 22, 2008 at 11:19 am

#187391

I have a stored procedure that I need to convert into a view. This sp makes use of two table variables that it then draws the final results from. When I try to do this in the view I get the following error:
Incorrect syntax near the keyword 'DECLARE'
Can you not use table variables in a View? Is there another way to handle this sort of thing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

A View consists of a single Select statement, only.

You can often transform procedural code into a single Select in unobvious ways, but this is always very case-specific. So in order to give more specific pointers, we would need to see your code.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Why do you need the proc turned into a view?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Save us all some time... post the stored procedure that you're trying to convert into a view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mark Harley SSCrazy Points: 2259 More actions · Answer 4

I've convinced the powers-that-be that this isn't something worth exploring since the proc works fine as is. Thanks for all of your help.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Hi,

Noramlly people convert he views to stored procedurs but here the case is bit opposite.

May i know what is it your trying to address?

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers