March 23, 2017 at 4:37 am
Using MS SQL (vers 2008R2, 2012, 2014)
Lately I have been getting my "feet wet" delving deeper and deeper into what I can do with SQL.
But I can't figure out how to do this.
I have a table [CASES], and it has many columns that are used for demographic stats (such as [IMPROVEMENT],[ZONE],[STATUS],[REFSOURCE] and so on.
So I wanted to retrieve a unique row and then pass these columns through an EXEC statement
Here is a cut down portion of the code. (I've simplified it to pull just one demographic column)
Select [CASES].[NUMBER], [CASES].[IMPROVEMENT] FROM [CASES] WHERE [CASES].[NUMBER] = @iCase;
IF @@Rowcount = 1 BEGIN
if @sStat = '6' BEGIN
--//IMPROVEMT
SET @Number = @iStatHdr;
SET @prog = 'CJ1';
SET @Mode = 'CS;';
SET @Field = [CASES].[IMPROVEMENT]
SET @Counter = 'QtyCount';
SET @Type = 'ClosedDuringPeriod_IMPROVE';
SET @Count = 1;
SET @Denominator = 0
EXEC LHStatFigs_22
@Number,
@prog,
@Mode,
@Field,
@Counter,
@Type,
@Count,
@Denominator;
.... and so on
The trouble I am having is passing the [CASES].[IMPROVEMENT] to the @Field variable. The CodeSense in SQL 2014 underlines it as an error. I suppose that I can use another SELECT statement to populate the @Field variable, but think that I should be able to use it from the unique row previously retrieved. Considering that there are about 24 different demographic codes (like [IMPROVEMENT]), I don't want to have to re-retrieve the same row over 24 times. What am I missing?
TIA
Mike
March 23, 2017 at 4:57 am
Strangely enough, this seems to work:
--Need to find Case --
Select [CASES].[NUMBER], [CASES].[IMPROVEMENT] FROM [CASES] WHERE [CASES].[NUMBER] = @iCase;
IF @@Rowcount = 1 BEGIN
if @sStat = '6' BEGIN
--//IMPROVEMT
EXEC LHStatFigs_22
@iStatHdr, --@Number,
'CJ1', --@Prog,
'CS;', --@Mode,
[IMPROVEMENT], --@Field,
'QtyCount', --@Counter,
'ClosedDuringPeriod_IMPROVE', --@Type,
1, --@Count,
0; --@Denominator;
not sure why the
"SET @Field = [IMPROVEMENT]" approach didn't work???
March 23, 2017 at 5:04 am
I've simplified your example further, so we have a table and data to play with:
DECLARE @Field VARCHAR(20);
CREATE TABLE #Cases(
Number INT NOT NULL,
Improvement VARCHAR(20) NOT NULL,
);
INSERT INTO #Cases
( Number, Improvement )
VALUES ( 1234, -- Number - int
'Brighter Pink' -- Improvement - varchar(10)
);
SELECT Number ,
Improvement FROM #Cases WHERE Number = 1234;
SET @Field = #Cases.Improvement;
DROP TABLE #Cases;
This doesn't work, because effectively the SELECT statement has been and gone. SQL Server made the selection, SSMS shows the results and it has moved on. The line trying to set @Field is using a format that SQL doesn't understand.
To store data extracted via a SELECT statement, you need to store it as part of the SELECT:
DECLARE @Field VARCHAR(20);
CREATE TABLE #Cases(
Number INT NOT NULL,
Improvement VARCHAR(20) NOT NULL,
);
INSERT INTO #Cases
( Number, Improvement )
VALUES ( 1234, -- Number - int
'Brighter Pink' -- Improvement - varchar(10)
);
SELECT @Field = Improvement FROM #Cases WHERE Number = 1234;
SELECT @Field;
DROP TABLE #Cases;
The change is that @Field has the value stored within it, within the SELECT. If you also want 'Number' then that will have to be stored in the same way:
DECLARE @Field VARCHAR(20);
DECLARE @Number INT;
CREATE TABLE #Cases(
Number INT NOT NULL,
Improvement VARCHAR(20) NOT NULL,
);
INSERT INTO #Cases
( Number, Improvement )
VALUES ( 1234, -- Number - int
'Brighter Pink' -- Improvement - varchar(10)
);
SELECT @Number = Number, @Field = Improvement FROM #Cases WHERE Number = 1234;
SELECT @Field;
SELECT @Number;
DROP TABLE #Cases;
March 23, 2017 at 5:36 am
Thanks for the explanation and excellent example
It makes sense the way that you have described it.
Mike
March 23, 2017 at 7:23 am
Based on the explanation (and a bit of trial and error), the following seems to do exactly what I had wanted with minimal redundant select statements:
DECLARE @DecisionToClose nVarChar(40);
DECLARE @Improvement nVarChar(40);
Select @decisionToClose = [CASES].[DECISIONTOCLOSE] , @Improvement = [CASES].[IMPROVEMENT] FROM [CASES] WHERE [CASES].[NUMBER] = 95000016;
SELECT @DecisionToClose
Select @Improvement
This effectively puts the returned row values into specific variables. I'm not sure what would happen if the select statement returned more than one row 🙁 (probably would be bad)
March 23, 2017 at 7:39 am
mcooper 15906 - Thursday, March 23, 2017 7:23 AMBased on the explanation (and a bit of trial and error), the following seems to do exactly what I had wanted with minimal redundant select statements:
DECLARE @DecisionToClose nVarChar(40);
DECLARE @Improvement nVarChar(40);
Select @decisionToClose = [CASES].[DECISIONTOCLOSE] , @Improvement = [CASES].[IMPROVEMENT] FROM [CASES] WHERE [CASES].[NUMBER] = 95000016;
SELECT @DecisionToClose
Select @ImprovementThis effectively puts the returned row values into specific variables. I'm not sure what would happen if the select statement returned more than one row 🙁 (probably would be bad)
Try it, with your test data. You'll see the error message that SQL returns.
March 23, 2017 at 8:07 am
Try it, with your test data. You'll see the error message that SQL returns.
I figured that it would with more than 1 row. In this case they are always unique rows, but I think that I will put a TOP 1 clause in just to be sure.
Thanks again, Steve...
I'm new to your forum but have developed relational database software (Dataflex) for 30 years ... just making the jump to SQL and having to retrain my brain on how the concept differs
Mike
March 23, 2017 at 10:13 am
mcooper 15906 - Thursday, March 23, 2017 8:07 AMTry it, with your test data. You'll see the error message that SQL returns.
I figured that it would with more than 1 row. In this case they are always unique rows, but I think that I will put a TOP 1 clause in just to be sure.
Thanks again, Steve...
I'm new to your forum but have developed relational database software (Dataflex) for 30 years ... just making the jump to SQL and having to retrain my brain on how the concept differs
Mike
If you know that the data should be unique then place a unique constraint on the appropriate column (I assume it will be the 'Number' column). Then you don't need to worry about choosing a random duplicate - the database enforces the required rule. After all, if you have more than one row with a selected 'Number' value and they both have different 'Improvement' values then which is correct? From the minimum examples here I would suggest a Clustered Index on 'Number' but the real picture that you see may be something else.
I have had access to a system (thankfully that I had no responsibility for whatsoever) where the database had nothing to prevent corrupt values in any of the data - all validation was controlled by the front-end software or within enormous and complex stored procedure. It was an incredibly complex package that nobody fully understood or wanted to work on, took exceptionally long to alter, generated obscene amounts of network traffic and had enormous project files. The database has a host of features to help keep the data in a useful fashion, so try and use them.
That brought back some horrid memories - I'm going for a lie-down in a darkened room now.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply