error procedure Please help me Urgent

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)

error procedure Please help me Urgent

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 03:48:49

Hi...
My procedure has the following error ..Please help me

create procedure dbo.[sp_update_table]
(
@update_date datetime,
@Ch_BoxList varchar(50)
)
as
begin
declare @date datetime
DECLARE @sql VARCHAR(1000)
set @date=getdate()
if(@date !=@update_date)
SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant' from tbl_merchant where login_name=@Ch_BoxList and updated_date=getdate()
EXEC @sql
end

error...
Msg 156, Level 15, State 1, Procedure sp_update_table, Line 12
Incorrect syntax near the keyword 'from'.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 04:08:25

Try the following. The highlighted part should be like this. YOu can't have FROM without DELETE Or SELECT

create procedure dbo.[sp_update_table]
(
@update_date datetime,
@Ch_BoxList varchar(50)
)
as
begin
declare @date datetime
DECLARE @sql VARCHAR(1000)
set @date=getdate()
if(@date !=@update_date)
SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ @Ch_BoxList +' and updated_date=getdate()'
EXEC @sql
end

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 04:25:05

The above will not give you any error while creating procedure but wont give you the desired result.
Te query that is going to be formed is:

select into table tbl_aa_merchant from tbl_merchant where login_name = aa and updated_date=getdate()
-- your login_name field is VARCHAR so it won't find a match for aa. It should be with quotes so,

The actual query that should be formed is:
select into table tbl_aa_merchant from tbl_merchant where login_name = 'aa' and updated_date=getdate()

Modify the Proc as

Alter procedure dbo.[sp_update_table]
(
@update_date datetime,
@Ch_BoxList varchar(50)
)
as
begin
declare @date datetime
DECLARE @sql VARCHAR(1000)
set @date=getdate()
if(@date !=@update_date)
SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ '''' + @Ch_BoxList + ''''' and updated_date=getdate()'
EXEC @sql
end

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 04:50:48

Hey what is the difference between these queries

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ @Ch_BoxList +' and updated_date=getdate()' and

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ '''' + @Ch_BoxList + ''''' and updated_date=getdate()'

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 05:27:40

alter procedure dbo.[sp_update_client]

(
@Ch_BoxList varchar(50)
)

as
begin
DECLARE @sql VARCHAR(1000)
SET @sql = 'truncate table tbl_'+@Ch_BoxList+'_merchant'
EXEC @sql
end
while executing this query i got this error( exec [sp_update_client]'amazon')

Msg 2812, Level 16, State 62, Procedure sp_update_client, Line 11
Could not find stored procedure 'truncate table tbl_amazon_merchant'.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 05:49:50

Hey please help me ..

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 05:58:11

select into table tbl_aa_merchant from tbl_merchant where login_name = aa and updated_date=getdate()
-- your login_name field is VARCHAR so it won't find a match for aa. It should be with quotes so,

The actual query that should be formed is:
select into table tbl_aa_merchant from tbl_merchant where login_name = 'aa' and updated_date=getdate()

quote:
Originally posted by jafrywilson

Hey what is the difference between these queries

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ @Ch_BoxList +' and updated_date=getdate()' and

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ '''' + @Ch_BoxList + ''''' and updated_date=getdate()'

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:00:29

Tnx for response...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:06:17

quote:
Originally posted by rohitvishwakarma

select into table tbl_aa_merchant from tbl_merchant where login_name = aa and updated_date=getdate()
-- your login_name field is VARCHAR so it won't find a match for aa. It should be with quotes so,

The actual query that should be formed is:
select into table tbl_aa_merchant from tbl_merchant where login_name = 'aa' and updated_date=getdate()

quote:
Originally posted by jafrywilson

Hey what is the difference between these queries

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ @Ch_BoxList +' and updated_date=getdate()' and

SET @sql = 'select into table tbl_'+@Ch_BoxList+'_merchant from tbl_merchant where login_name = '+ '''' + @Ch_BoxList + ''''' and updated_date=getdate()'

Hey see this

update tbl_user set pwd=@pwd_new where login_name=@login_name this my query in a SP..It works fine .

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:07:07

Then why we need to give quotes for this... login_name = 'aa'

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:07:55

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 06:21:55

quote:
Originally posted by jafrywilson

Then why we need to give quotes for this... login_name = 'aa'

because if used normally

DECLARE @somevalue VARCHAR(10)
SET @somevalue ='thisvalue'

SELECT * FROM sometable WHERE column_1 = @somevalue
works fine

but in

SET @sql= SELECT * FROM sometable WHERE column_1 ='+ @somevalue
PRINT(@sql)

you can see the o/p is
SELECT * FROM sometable WHERE column_1 = thisvalue

If you do

SET @sql= SELECT * FROM sometable WHERE column_1 ='+ ''''+ @somevalue +''''
PRINT(@sql)

o/p is
SELECT * FROM sometable WHERE column_1 = 'thisvalue'

This is done to add quotes to VARCHAR variable.

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:25:27

Thank you for the response... I understand about this

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 06:33:55

TRY this, it will work. Notice the highlighted part

alter procedure dbo.[sp_update_client]

(
@Ch_BoxList varchar(50)
)

as
begin
DECLARE @sql VARCHAR(1000)
SET @sql = 'truncate table tbl_'+@Ch_BoxList+'_merchant'
EXEC(@sql)
end

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:40:09

thank you so much ... It works...

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-17 : 06:41:01

thank you for your kindly help...It is very useful for me..

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-17 : 06:43:46

EXEC command executes stored procedure where as EXEC() function takes dynamic string as input and executes them