Creating a view with Declare

Question

Post reply

Creating a view with Declare

bawinkley

SSC Enthusiast

Points: 136
More actions
July 22, 2013 at 11:29 pm

#395183

I am trying to create a view with the below query.
it should be a view as data in other tables will be updated and I want to refer to this often. Or as a last resort schedule the creation of this table over night some how?
DECLARE @@string1 varchar (20) = ' Number='
DECLARE @@string2 varchar (20) = ' And Id='
SELECT f.linkid, f.docname, f.title, dc.catname, t.template, r.extlink ,f.libid
,substring(extlink, charindex(@@string1, extlink)+len(@@string1),
charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink)) as ID
from AL_TEMPLATE t
INNER JOIN AL_RELATION r ON t.tempID=r.tempid
INNER JOIN AL_RELATED_FILE rf ON r.relID=rf.relid
INNER JOIN al_file f ON rf.linkid=f.linkID
INNER JOIN al_filecats fc ON f.linkid=fc.linkid
INNER JOIN AL_DOCCAT dc ON fc.catid=dc.catID
where charindex(@@string2, extlink) -len(@@string1) - charindex(@@string1, extlink) > 0
Thanks

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

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

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

A view can only contain a single select statement, no other statements are allowed.

Try an in-line table-valued function with those two variables as parameters to the function.

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

bawinkley SSC Enthusiast Points: 136 More actions · Answer 2

I'm pretty sure you cant use variables in views, I have decided to take them out which isn't the end of the world.

I haven't worked out how to create a table on a scheduled task yet but i think the above method is better for this purpose.

Thanks

bawinkley SSC Enthusiast Points: 136 More actions · Answer 3

sorry GilaMonster, didn't see your response,

um - ok I'll have a look at that thanks!

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

bawinkley (7/23/2013)
I'm pretty sure you cant use variables in views, I have decided to take them out which isn't the end of the world.
I haven't worked out how to create a table on a scheduled task yet but i think the above method is better for this purpose.
Thanks

As Gail already suggested you can use inline table valued function and can pass those two parameteres in that..........

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/