October 2, 2007 at 9:47 pm
Hi there, in my function i want to make certain standard querry string.
my sample code,
DECLARE @tbl_1 char(500)
SET @tbl_1 = 'Declare myTable1 Cursor FOR SELECT a, b, c FROM abc'
...
EXEC @tbl_1
...
there is no error when creating this function, however when I call this function it returns error
" Declare myTable1 Cursor FOR SELECT a, b, c FROM abc' is not a valid identifier.
Where is wrong? Can any pro guides me on this?
Thank you,
Wilson
October 2, 2007 at 10:05 pm
There's no easy way to say it...
Seems like the design is flawed from my current view of the problem. Can you provide more details about what you are trying to do so that we can point you out to the best solution?
October 2, 2007 at 10:34 pm
Hi Ninja, thanks for your reply, I actually want to querry the same table with diffrent expression.
Hope you can follow sample code below,
...
DECLARE @tbl_1 char(500), @tbl_2 char(500), @tbl_3 char(500)...
SET @tbl_1 = 'Declare myTable1 Cursor FOR SELECT a, b, c FROM abc where a!=0 '
SET @tbl_2 = 'Declare myTable2 Cursor FOR SELECT a, b, c FROM abc where a=myTable1.a '
SET @tbl_3 = 'Declare myTable2 Cursor FOR SELECT a, b, c FROM abc where a=myTable2.a '
...
EXEC @tbl_1
Open myTable1
FETCH NEXT FROM myTable1 INTO @code_a, @code_b, ...
WHILE @@FETCH_STATUS=0
INSERT @resultTabls
SELECT @code_a, @code_b, ...
EXEC @tbl_2
Open myTable2
FETCH NEXT FROM myTable1 INTO @code_a, @code_b, ...
WHILE @@FETCH_STATUS=0
INSERT @resultTabls
SELECT @code_a, @code_b, ...
EXEC @tbl_3
Open myTable3
FETCH NEXT FROM myTable1 INTO @code_a, @code_b, ...
WHILE @@FETCH_STATUS=0
INSERT @resultTabls
SELECT @code_a, @code_b, ...
...
...
Thank you
October 2, 2007 at 10:38 pm
Make 3 different procs to do the selects.
In the main proc, do something like this :
Insert into @HoldingResults (Cols, list) EXEC dbo.CallTheRightProc @Param1, @ParamX
You just have to call the right sub proc depending on the context. That'll get you the best code overall.
October 3, 2007 at 12:41 am
Good idea!
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply