Dynamic SQL Error - 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 2000 Forums

Transact-SQL (2000)

Dynamic SQL Error

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 12:08:14

hi
CREATE PROCEDURE ABC @flnm varchar(40)
AS
Declare @y varchar(40)
Declare @x varchar(40)
Declare @sqint varchar(1000)
Declare @aa varchar(10)

Set @aa='%'
set @x = @flnm
Set @y='abc_' + @x

set @sq=

'INSERT INTO '+ @y +
'SELECT Distinct C.Field1,C.Filed2,C.Field3
FROM table1 as C left Outer JOIN table2 AS G
ON C.EMAIL = G.EMAIL'

EXEC(@sq)

EXEC abc fn123
iam using above code, when i complie the proc there isn't any error but when i execute it , it gives me error on select command. Note when i use this commant with hard coded table name then its working fine

Can any one help Me Thanks

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-02 : 12:18:51

You need a space before the SELECT because I believe the string constructed is "...abc_tablenameSELECT..."

Because the stored proc is building a string for execution it will compile, but that doesn't mean the SQL statement created is syntactically correct.

You can use PRINT @SQL to see if your string is correct.

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 12:44:13

Thanks

Now my real query is this one
set @sq=
'INSERT INTO '+ @y +
'SELECT Distinct C.Field1,C.Filed2,C.Field3
FROM table1 as C left Outer JOIN table2 AS G
ON C.EMAIL not like '%'G.EMAIL'%''

but when i complie it gives me an error on on G.email. iknow I am not using it in a right way but can u help me in writing me this wildcard thing
Thnaks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 13:04:00

NOT LIKE %' + G.EMAIL + '%'

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 13:14:08

thanks

The column prefix 'G' does not match with a table name or alias name used in the query.

I have this error, Can u plz fix it

remember when I am hrdcoding and using
ON C.EMAIL NOT LIKE '%' G.EMAIL '%' I dont have any error
Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 13:54:19

Can you show us the whole thing? That way I can pull it up on my machine quickly.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-02 : 13:59:36

[code]ON C.EMAIL not like ''%''+G.EMAIL+''%'''[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 14:04:04

Yes I forgot the other single quotes. But that doesn't explain the error now though. Why can't it see the G alias? I have never seen NOT LIKE %<string>% in the JOIN portion. Isn't there a better way to write it?

Tara

Granick
Starting Member

46 Posts

Posted - 2003-12-02 : 14:15:32

Looks like it is trying to insert all records where the Email doesn't already exist? If that is the case, then would it be something like this?

set @sq=
'INSERT INTO '+ @y +
'SELECT Distinct C.Field1,C.Filed2,C.Field3
FROM table1 as C left Outer JOIN table2 AS G
ON C.EMAIL = G.EMAIL
WHERE G.EMAIL IS Null'

If that is not what is trying to be done, I don't think you can write the NOT LIKE as part of a join clause unless you are maybe using it as part of a second conditional statement. So if you joined on EMAIL and [SecondField] NOT LIKE '%[SecondValue]%', or something like that.

Does that make sense?
Shannon

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 14:22:20

hi
I have to check the two email filds with 'not like' operator, idid this by joing c.email=g.emil where c.email not like g.email. But didn't get the correct result se
So when I joined the the two table with 'not like' my answer was OK. The problem is that I have restriction to use use like.
Now My problem is to use like in a proper way in a dynmic sql
Thanks

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 14:27:49

Thanks you guys
So Nice of u people

Now this is working
not like ''%''+G.EMAIL+''%'''

TARA do u have any suggestion for me for my join
Thanks Again
Asim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 14:32:12

I would need to see some data. I'm kinda confused as to what you are trying to accomplish. Show us just a few rows from each table and what you want the results to show.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 14:45:24

table2 G
@abcdefg.com
@abcwc.com
@abuse.net
abcd@abuse.net
Table1 C
abddfr@yahoo.com
abcss@yahoo.com
abc@cox.net

so i have to check that does table1 contains the above record(table2), whether its a full email address or only domain part

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 14:53:16

Using the sample data that you provided (probably need to rework the sample data so that you do have matches), what should the query return?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources