assigning values to a variable using select into

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)

assigning values to a variable using select into

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 15:34:06

Hi,

I am trying to pass a parameter to the stored proc and assign it a value when selecting from another table using select into. How do I workaround this . Below is the error message I receive.

A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.

This statement is the issue @SizeBucket is passed in as a parameter to the stored proc:

@SizeBucket = CASE
WHEN iql.USDSize <1000 THEN '<1000'
WHEN iql.USDSize >= 1000 AND iql.USDSize <5000 THEN '>= 1000 & <5000'
WHEN iql.USDSIZE >=5000 AND iql.USDSize <10000 THEN '>= 5000 & <10000'
ELSE '>= 10000' END,

SELECT
MISInquiryID = trd.MISInquiryID,
ClientID = PPivotalCompanyID,
DealerId = CPPivotalCompanyID,
ProtocolID = trd.ProtocolID,
CPLevel = trd.Level,
TradeMonth = right('0' + convert(varchar(2), datepart(mm, trd.TradeDate)), 2),
TradeMonthName = datename(mm, trd.TradeDate),
TradeYear = convert(varchar(4), datepart(yy, trd.TradeDate)),
Product = prd.ShortName,
ClientType = dlr.MA_Type,
@SizeBucket = CASE
WHEN iql.USDSize <1000 THEN '<1000'
WHEN iql.USDSize >= 1000 AND iql.USDSize <5000 THEN '>= 1000 & <5000'
WHEN iql.USDSIZE >=5000 AND iql.USDSize <10000 THEN '>= 5000 & <10000'
ELSE '>= 10000' END,
@IsBuy = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'N/A' END
INTO
#Trade
FROM
Trade trd
INNER JOIN
pivotal..Company dlr
ON
trd.CPPivotalCompanyID = dlr.Company_Id --same as ClientID which is alias
INNER JOIN
Product prd
ON
trd.ProductID = prd.ProductID
INNER JOIN
Inquiry inq
ON
trd.MISInquiryID = inq.MISInquiryID
INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

where
datepart(yy, trd.TradeDate) = datepart(yy, @EndTradeDate) --2009
--and datepart(mm, trd.TradeDate) > 6
--and trd.PPivotalCompanyID = CONVERT(binary(8), 50)
--and trd.ProtocolID = 1
and trd.ProtocolID = @ProtocolID --(values 1,2,3,4)
and trd.IsVolume = 1
and NOT trd.CPPivotalCompanyID in (select DealerID from #Final)
and trd.ProductID = @ProductID --in (1,2)
and trd.ProductID in (1,2,3,17,4,18,6,7,19,20)
and dlr.MA_Type = @ClientType
and trd.ListID = @ListID

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 16:56:35

Is there another option for inserting into a table when assigning values to a variable?

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 18:40:58

can someone please help tell me how to insert assigned variables into a table using select into?

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 18:47:54

SELECT can not do both Select values (i.e. producing a resultset) AND Assign to @variables. One or the other.

UPDATE can assign values to columns AND assign values to @variables

You would have to insert into temporary table, select from that for your INSERT and re-select from it for your Assign

Why do want to assign to variables? The two you are assigning are not use (in the sample of code you have shown), and where multiple rows were involved the final value would be "random"

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-25 : 19:46:43

The two variables will be displayed on another GUI for users to choose the values. We need these values.

Inserting into is the only way?

I am already inserting in the temp table as in the code pasted but I will take out the variables. I believe you are saying that I should put the result from the first insert into another temporary table and then assign variables? Can you please clarify? Thanks.

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 03:49:30

[code]
INSERT INTO #TempTable or @TableVar
SELECT ...
FROM ...

SELECT ...
INTO #Trade
FROM #TempTable or @TableVar

SELECT @SizeBucket = ...
@IsBuy = ...
FROM #TempTable or @TableVar
[/code]
Or maybe you can store the values in #Trade so you have
[code]
SELECT MISInquiryID = trd.MISInquiryID,
...
[TEMP_SizeBucket] = CASE
WHEN iql.USDSize <1000 THEN '<1000'
WHEN iql.USDSize >= 1000 AND iql.USDSize <5000 THEN '>= 1000 & <5000'
WHEN iql.USDSIZE >=5000 AND iql.USDSize <10000 THEN '>= 5000 & <10000'
ELSE '>= 10000' END,
[TEMP_IsBuy] = CASE trd.IsBuy WHEN 1 THEN 'Bid' WHEN 0 THEN 'Offer' ELSE 'N/A' END
INTO #Trade
FROM Trade trd
...

SELECT @SizeBucket = TEMP_SizeBucket,
@IsBuy = TEMP_IsBuy
FROM #TempTable or @TableVar
[/code]

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 03:50:52

P.S. Do you only have ONE row selected into #Trade? Otherwise


SELECT @SizeBucket = ...
       @IsBuy = ...

is meaningless / random

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-26 : 11:13:13

No not one row. A bunch of rows. I used your logic above with [Temp_SizeBucket]. Now the stored proc is passed the parms @SizeBucket and @IsBuy. Would I need to assign the variables after the insert into #Trade table?

I ran the query and the insert stored the range of values in the #Trade tables as the columns Temp_SizeBucket and Temp_IsBuy.

Thanks a lot for your help. Appreciate it.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-26 : 11:26:47

I exected the stored procedure, get the following error

Msg 208, Level 16, State 0, Procedure rptAvgSpreadToCoverRatio, Line 198
Invalid object name '#TempTable'.

I assigned the variables like so : Is it not finding the table in Tempdb?

--Assign values to SizeBucket and IsBuy variables
SELECT @SizeBucket = TEMP_SizeBucket,
@IsBuy = TEMP_IsBuy
FROM #TempTable

Execute rptAvgSpreadToCoverRatio '20090801', '20090901','20100223','946094', 'Insurance/Reinsurance', 1, 2, 1,1000
/*(
@BeginTradeDate varchar(10), -- 20100801
@EndTradeDate varchar(10), -- 20100901
@ThresholdDate varchar(10), --TradeDate from Trade Table for New/Historic Dealer Historic Dealer: Date (Check First Trade. If First Trade is after selected date, then this is a NEW Dealer. If First Trade is before selected date, then this is a HISTORIC Dealer)
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@ClientType varchar(30), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@ProductID tinyint, --map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ProtocolID tinyint, --Single Select Limit to SpreadProtocol=1, PriceProtocol=2, YieldProtocol=3, DMProtocol=4
@IsBuy bit, --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100) */

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-26 : 11:29:15

Oops SORRY. i shold have said SELECT @SizeBucket = TEMP_SizeBucket,
@IsBuy = TEMP_IsBuy
FROM #Trade.

I got rid of that error.

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 14:01:05

" A bunch of rows"

Did you understand my earlier point that the values you will get in @SizeBucket and @IsBuy will be totally unpredictable, and "random" ?

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-28 : 06:45:05

Yes. For example the values will be as below. We have another GI to which these parms will be passed. Users expect to see a drop down for SizeBcket(Single Select Bids and Offers, Bids, Offers) and IsBuy (<1million, 1-5 million, 5 million-10 million, >10 million). The GUI has the logic for the dropdown list.

Another question. The stored proc returns 0 rows. One of the reasons I believe is that I am passing a BIT value but assigning a varchar to the IsBuy variable, I believe I need to use the convert function when assigning the variable correct? What is the exact syntax?

Execute rptAvgSpreadToCoverRatio '20090801', '20090901','20000505','2222961','Broker-Dealer', 1, 1, 1,900
/*(
@BeginTradeDate varchar(10), -- 20100801
@EndTradeDate varchar(10), -- 20100901
@ThresholdDate varchar(10), --TradeDate from Trade Table for New/Historic Dealer Historic Dealer: Date (Check First Trade. If First Trade is after selected date, then this is a NEW Dealer. If First Trade is before selected date, then this is a HISTORIC Dealer)
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@ClientType varchar(30), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@ProductID tinyint, --map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ProtocolID tinyint, --Single Select Limit to SpreadProtocol=1, PriceProtocol=2, YieldProtocol=3, DMProtocol=4
@IsBuy bit, --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100) */
I tried the convert but it does not work. The error msg is

Msg 245, Level 16, State 1, Procedure rptAvgSpreadToCoverRatio, Line 198
Conversion failed when converting the varchar value 'Bid' to data type bit.

Size IsBy
<1000 Bid
<1000 Offer
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Offer
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Bid
<1000 Offer
<1000 Offer
<1000 Offer
<1000 Offer
>= 1000 & <5000 Bid
>= 1000 & <5000 Bid
<1000 Bid
>= 5000 & <10000 Bid

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-28 : 06:51:08

This is where the error occurs: I am not sure how to convert the varchar.

--Assign values to SizeBucket and IsBuy variables
SELECT @SizeBucket = TEMP_SizeBucket,
@IsBuy = TEMP_IsBuy
FROM #Trade

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-28 : 07:12:23

OK I passed the parameter @IsBuy as a varchar(20) instead of BIT. Now the stored proc returns just one row but atleast it returns data.

Execute rptAvgSpreadToCoverRatio '20090801', '20090901','20000505','2222961','Broker-Dealer', 1, 1, 1,900
/*(
@BeginTradeDate varchar(10), -- 20100801
@EndTradeDate varchar(10), -- 20100901
@ThresholdDate varchar(10), --TradeDate from Trade Table for New/Historic Dealer Historic Dealer: Date (Check First Trade. If First Trade is after selected date, then this is a NEW Dealer. If First Trade is before selected date, then this is a HISTORIC Dealer)
@ListID varchar(50), --(Single-Select): All Inquiries; Single Inquiries; Lists Single or Multiple Lists
@ClientType varchar(30), --(Multi-Select): All Client Types, Mutual Funds, Insurance Companies, etc. MA_Type single or multile from pivotal..Company table (tables Dealer,Trade, Response)
@ProductID tinyint, --map to ProtocolID values 1-4 Product (Multi-Select): Limit to USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF. The product selection list should be sorted in the following order:USHG; FRN; EM; EMLM; HY; HYC; AGNCY; AGNCYF; USFD; USFF
@ProtocolID tinyint, --Single Select Limit to SpreadProtocol=1, PriceProtocol=2, YieldProtocol=3, DMProtocol=4
@IsBuy varchar(20), --Single Select Bids and Offers, Bids, Offers
@SizeBucket varchar(100) */

Client TradeMonthName TradeYear ExecuteToCoverDiff ResponseCount TradeMonth

NULL February 2009 11.0275 3.91666666666667 02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources