Where M I wrong ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where M I wrong ???

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 04:20:46

Sorry in advance for posting frequently as I'm preparing a batch job function.

The procedure is like
-Extract records that meet specific condition (Select Statement)
-Exec another sp passing parameter values from select statement (Exec SP passing params)
-Update some tables where some cols in select statement meet some condition. (Update Statement)

I confuse how to relate these 3 matters together.
Now I wrote like

IF EXISTS(
SELECT .... from ...TblA...Join...Join..Join... where ....
)
BEGIN

EXEC Sp @p1 = replace (..., 'test', TblA.No), @p2=....,@p3=...

Update Set .. Where....

END

Getting Error : "The multi-part identifier "TblA.No" could not be bound."
Pointing to bold line...

Where M I wrong ???

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 04:25:33

If you need to execute dynamic sql you'll need to create a valid sql query in a variable first:

DECLARE @sql nvarchar(max)

SET @sql = 'select something from table'
EXEC @sql

Same with parameters...you can't buiold your parameters directly in the exec statement, you'll have to do it in advance.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 04:35:11

DECLARE @SUB VARCHAR(8000);
SET @SUB= REPLACE('some text','test',Tbl1.col)
EXEC SP @Param1 = @SUB

Still the same error......

quote:
Originally posted by Lumbago

If you need to execute dynamic sql you'll need to create a valid sql query in a variable first:
DECLARE @sql nvarchar(max)

SET @sql = 'select something from table'
EXEC @sql
Same with parameters...you can't buiold your parameters directly in the exec statement, you'll have to do it in advance.

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 04:40:21

Then post the actual code you're using. SET @SUB= REPLACE('some text','test',Tbl1.col) doesn't make sence because "Tbl.col" is not referencing anything...

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 04:42:56

Is this what you're trying to do? ->

UPDATE Tbl1 SET Col = REPLACE(Col, 'some text', 'test) WHERE ID IN (...)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-11 : 04:49:32

Something like this maybe? ->

set @SUB = (
  SELECT TOP 1 REPLACE(P1.PARAMVAL, '<refno>', f.refno)
  FROM CFMS_SYS_PARAMS P1 
    INNER JOIN CFMS_FEEDBACK F
      ON ...your join condition...
  WHERE P1.PARAMGROUP='OVERDUE_ALERT' 
    AND P1.PARAMCODE ='SUBJECT')

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-11 : 05:07:48

quote:

If you need to execute dynamic sql you'll need to create a valid sql query in a variable first:

DECLARE @sql nvarchar(max)

SET @sql = 'select something from table'
EXEC @sql

You need to enclose the variable in parenthasis!


EXEC (@sql)

Otherwise it won't work.

Also -- you are MUCH better off using EXEC sp_executeSql

I can't see a dynamic sql call though in the code posted?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 19:26:21

still got same error..

quote:
Originally posted by Transact Charlie

quote:

If you need to execute dynamic sql you'll need to create a valid sql query in a variable first:

DECLARE @sql nvarchar(max)

SET @sql = 'select something from table'
EXEC @sql

You need to enclose the variable in parenthasis!

EXEC (@sql)

Otherwise it won't work.

Also -- you are MUCH better off using EXEC sp_executeSql

I can't see a dynamic sql call though in the code posted?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 20:08:22

I can't find the part "EXEC Sp @p1 . . ." in the query you posted.

Can you post your full query and the exact error message ?

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 20:16:04

I posted the full query. "EXEC Sp @p1 . . ." is just example.

I m getting error at the bold line and you can see I pass @SUB as parameter to storedproc. Actually I w like to access the fields and tables from select statement in EXEC ... and Update.

Pls help me. tks.

quote:
Originally posted by khtan

I can't find the part "EXEC Sp @p1 . . ." in the query you posted.

Can you post your full query and the exact error message ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 20:36:50

the query

SELECT P1.PARAMVAL FROM CFMS_SYS_PARAMS P1 WHERE P1.PARAMGROUP='OVERDUE_ALERT' AND P1.PARAMCODE ='SUBJECT'

will return multiple rows ?

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 20:41:10

No. There is only one record for that condition

quote:
Originally posted by khtan

the query
SELECT P1.PARAMVAL FROM CFMS_SYS_PARAMS P1 WHERE P1.PARAMGROUP='OVERDUE_ALERT' AND P1.PARAMCODE ='SUBJECT'

will return multiple rows ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 20:49:09

[code]DECLARE @SUB VARCHAR(8000);
set @SUB= REPLACE((SELECT P1.PARAMVAL FROM CFMS_SYS_PARAMS P1 WHERE P1.PARAMGROUP='OVERDUE_ALERT' AND P1.PARAMCODE ='SUBJECT'),'<refno>',f.refno)[/code]

where is the f.refno coming from ?

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 20:51:57

Pls see 'FROM CFMS_FEEDBACK F ' in select statement. There is column name refno in CFMS_FEEDBACK .

quote:
Originally posted by khtan

DECLARE @SUB VARCHAR(8000);
set @SUB= REPLACE((SELECT P1.PARAMVAL FROM CFMS_SYS_PARAMS P1 WHERE P1.PARAMGROUP='OVERDUE_ALERT' AND P1.PARAMCODE ='SUBJECT'),'<refno>',f.refno)

where is the f.refno coming from ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 21:04:24

sorry i am totally lost. Can you explain what is the intention of your query ?

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 21:08:52

-Extract records that meet some condition (Select Statement)
-Exec another storedproc (CFMS_Enqueue_Mail ) passing some parameter values which should come from select statement (Exec )
-Update some tables upon successful execution of CFMS_Enqueue_Mail . (Update Statement)

quote:
Originally posted by khtan

sorry i am totally lost. Can you explain what is the intention of your query ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 21:22:19

looks like you will need some looping or using cursor


declare cursor . . .
open cursor
fetch next . .

while  @@fetch_status = 0
begin
    exec CFMS_Enqueue_Mail 
    update FDD 
    fetch next . .
end
close cursor
deallocate cursor

KH
[spoiler]Time is always against us[/spoiler]

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-11 : 21:32:25

Meaning I dont need to use If Exist ??

quote:
Originally posted by khtan

looks like you will need some looping or using cursor

declare cursor . . .
open cursor
fetch next . .

while @@fetch_status = 0
begin
exec CFMS_Enqueue_Mail
update FDD
fetch next . .
end
close cursor
deallocate cursor

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-11 : 21:53:03

i guess so from what i understand . . . i don't know that is the purpose of IF EXISTS() in your code.

KH
[spoiler]Time is always against us[/spoiler]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-12 : 02:05:40

Have you tried what I suggested here?? ->

quote:
Originally posted by Lumbago

Something like this maybe? ->
set @SUB = (
SELECT TOP 1 REPLACE(P1.PARAMVAL, '<refno>', f.refno)
FROM CFMS_SYS_PARAMS P1
INNER JOIN CFMS_FEEDBACK F
ON ...your join condition...
WHERE P1.PARAMGROUP='OVERDUE_ALERT'
AND P1.PARAMCODE ='SUBJECT')

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources