Need urgent help - 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

General SQL Server Forums

New to SQL Server Programming

Need urgent help

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-28 : 19:24:13

Here is my stored procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Sp_PriceReport]
@usname varchar(100),@name varchar(50)
as

begin

SELECT MobID,
MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS MyPrice
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS low,
CASE Merchant
WHEN @usname THEN 1
ELSE 0
END AS My
FROM tbl_'+@name+'_master_merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

SELECT DISTINCT t1.MPN_SKU,
t1.Product_Name,
m.MyPrice,
m.LowPrice,
m.LowPriceMerchant,
m.HighPrice,
m.HighPriceMerchant

FROM ##Temp AS m
INNER JOIN tbl_'+@name+'_master_product AS t1 ON t1.MobID = m.MobID

drop table ##temp
end

While executing this i got this error
Msg 102, Level 15, State 1, Procedure Sp_PriceReport, Line 27
Incorrect syntax near '+@name+'.
Msg 102, Level 15, State 1, Procedure Sp_PriceReport, Line 41
Incorrect syntax near '+@name+'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 19:36:47

You need dynamic SQL for that: http://www.sommarskog.se/dynamic_sql.html

But you really shouldn't create a stored procedure like that. Why does the table name need to be dynamic?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-28 : 19:41:36

There are 9 tables like tbl_wilson_master_merchant,tbl_wilson_master_product,tbl_jafry_master_merchant,tbl_jafry_master_product....
So i need for a dynamic query to solve this problem..

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-28 : 19:42:55

Actually I created tbl_master_merchant... But now I need like this .. Pls help me ..

kevlangdo
Starting Member

5 Posts

Posted - 2010-09-28 : 21:32:18

Maybe you could try retrieving the table from sys.tables or sys.objects and assigning it to a Type as Table.

Then use the table variable

Kevin Languedoc
Sr BI Developer
www.kcodebook.com/wp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 22:46:09

Ummm, just look at the link I posted on how to do it dynamically.

You've got a design issue if you have tables like that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-29 : 04:27:32

here it is:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Sp_PriceReport]
@usname varchar(100),@name varchar(50)
as

begin 

DECLARE @sql VARCHAR(1000)
SET @sql = '
SELECT MobID,
MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS MyPrice
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS low,
CASE Merchant
WHEN @usname THEN 1
ELSE 0
END AS My
FROM tbl_' + @name + '_master_merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

SELECT DISTINCT t1.MPN_SKU,
t1.Product_Name,
m.MyPrice,
m.LowPrice,
m.LowPriceMerchant,
m.HighPrice,
m.HighPriceMerchant

FROM ##Temp AS m
INNER JOIN tbl_'+@name+'_master_product AS t1 ON t1.MobID = m.MobID

drop table ##temp'
end

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 13:30:41

Hey rohit,
I got this error
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@usname".

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 13:35:11

How to assign value to usname

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:00:32

I tried this WHEN '+@usname+' THEN 1 and got this error Msg 207, Level 16, State 1, Line 16
Invalid column name 'teptronics'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 14:06:47

teptronics isn't in the code that he posted, so please post the code that you've now got.

Also, please do not use sp_ as the prefix for stored procedures. That is a performance problem as SQL automatically will check the master database first for the object. Never use sp_ is my recommendation, although other people will say you can use it for user-created system objects. I avoid sp_ like the plague!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:09:23

exec [Sp_PriceReport] 'teptronics','shopzilla' ... This way I executed my SP

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:10:56

Thanks for your advice ..Hereafter I will keep it in mind when creating procedures

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:12:15

He missed one line before end statement EXEC(@sql)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 14:18:26

So is teptronics a column in the tbl_shopzilla_master_merchant table?

Run this and post the output:

use YourDbName
go

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'tbl_shopzilla_master_merchant'
order by ORDINAL_POSITION

The @usname part of your code doesn't make sense to me.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:24:08

These are the columns names
id
MOBID
Bottomline_Price
Condition
Featured_Store
Free_Shipping
Hacker_Safe
Logo
Merchant
Merchant_Rating
Price
Rank_After_Sorting
Rank_Before_Sorting
Shipping
Store_Like
Tax
Zip_Def_Rank
Zip_Sorted_Rank
Updated_Date
Updated_Time
Crawl_Source
Reviews

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 14:26:17

teptronics doesn't exist in your table, hence the error.

So what are you trying to do with that bit of code? It needs be modified, but I'm unsure how to modify it as I'm not clear on what you want for that part (@usname).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:27:46

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Sp_PriceReport2]
@usname varchar(100)
as

begin

SELECT MobID,
MAX(CASE WHEN low = 1 THEN Merchant ELSE NULL END) AS LowPriceMerchant,
MAX(CASE WHEN low = 1 THEN Price ELSE NULL END) AS LowPrice,
MAX(CASE WHEN high = 1 THEN Merchant ELSE NULL END) AS HighPriceMerchant,
MAX(CASE WHEN high = 1 THEN Price ELSE NULL END) AS HighPrice,
MAX(CASE WHEN My = 1 THEN Price ELSE NULL END) AS MyPrice
INTO ##Temp
FROM (
SELECT MobID,
Merchant,
Price,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price DESC) AS high,
ROW_NUMBER() OVER (PARTITION BY MobID ORDER BY Price) AS low,
CASE Merchant
WHEN @usname THEN 1
ELSE 0
END AS My
FROM tbl_master_merchant
) AS t
GROUP BY MobID
HAVING MAX(My) = 1

SELECT DISTINCT t1.MPN_SKU,
t1.Product_Name,
m.MyPrice,
m.LowPrice,
m.LowPriceMerchant,
m.HighPrice,
m.HighPriceMerchant

FROM ##Temp AS m
INNER JOIN tbl_master_product AS t1 ON t1.MobID = m.MobID

drop table ##temp
end

This is my actual SP..It generates my expected output .. My need is to use the same sp for other 9 CSE so i created dynamic query for creating table names dynamically..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 14:34:26

You need this: WHEN ''' + @usname + ''' THEN 1

The extra single quotes are because it's a data value and not an object name.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-29 : 14:35:28

This quick example should illustrate what I mean:

declare @sql varchar(100), @usname varchar(50)

set @usname = 'test'

set @sql = 'WHEN ''' + @usname + ''' THEN 1'

print @sql

set @sql = 'WHEN ' + @usname + ' THEN 1'

print @sql

Look at the output of my quick example and notice the difference of "test".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-09-29 : 14:37:53

I already tried this WHEN '+@usname+' THEN 1 and got this error Msg 207, Level 16, State 1, Line 16
Invalid column name 'teptronics'.

Because it is inside of the dynamic query ..What can we do ? to solve this

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources