Dynamic SQL - 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

Author

Topic

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-07-21 : 10:01:43

Ok, here's my question. Is it possible to index variables within a SQL string? Basically, I am trying to write a procedure that will loop X times and output the results to different variables based on the index. Its mainly a work around for the fact that SQL does not support arrays. The program that I use to interface with the stored procedure does not support recordsets so I have to output the results to variables.

When I run the sample below in QA, the SQL looks good, but I get the following error - Must declare the variable '@SKU_01'.

Here is a sample of the code that I have been using in QA.


DECLARE @SKU_01 INT
DECLARE @RSID_01 INT
DECLARE @PT_01 INT
DECLARE @TID_01 INT

DECLARE @SQL  VARCHAR(500)	-- SQL String
DECLARE @Index INT

SET @Index = 1

-- Searches for the lowest RSID for a given cell that has been released and has not been pulled
SET @SQL = 
'SELECT @SKU_0' + CAST (@Index AS VARCHAR) + ' = SKU,
	@RSID_0' + CAST (@Index AS VARCHAR) + ' = RSID, 
	@PT_0' + CAST (@Index AS VARCHAR) + ' = SCID, 
	@TID_0' + CAST (@Index AS VARCHAR) + ' = TID 
FROM ReleaseSequencing 
	WHERE (CID = ' + CAST (@Index AS VARCHAR) + ') AND 
	(PLID % ' + CAST (@Index AS VARCHAR) + ' = 0) AND 
	(Released = 0) AND 
	RSID = (SELECT MIN(RSID) 
		FROM tblReleaseSequencing 
		WHERE (CID > 0) AND (PLID % ' + CAST (@Index AS VARCHAR) + ' = 0)) 
ORDER BY RSID'
		
PRINT @SQL

EXECUTE (@SQL)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 10:21:31

When you use dynamic SQL, you have to declare the variables inside of the dynamic SQL. You cannot index variables. You can declare a primary key on a table variable and index temp tables. That's about as close as you can get though. I probably just haven't read your question right as I'm a little busy right now. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-07-21 : 10:43:46

Ok, I moved everything into @SQL and it worked. Unfortunately the interfacing program cannot access the dynamic variables. I guess I will just have to hardcode the iterations rather than loop through them.

Thanks

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-21 : 13:04:06

What front-end are you using that supports stored procedures but not result-sets? What can you do from the front end? An option might be to return each column list as comma delimited strings and load them into Arrays in your app (assuming it can do that) Can you pass an item list as a paramater (@Index as Varchar)? Since you were prepared to hard code the iterations, 8000 bytes should be enough to hold all requests.


DECLARE @SKU Varchar(8000)
DECLARE @RSID Varchar(8000)
DECLARE @PT Varchar(8000)
DECLARE @TID Varchar(8000)
DECLARE @CID Varchar(8000)

DECLARE @SQL  VARCHAR(500)	-- SQL String
DECLARE @Index Varchar(8000)

SET @Index = '1,4,6,8,10,32,87'

SELECT  @CID  = Coalesce (@CID  +',','') + Convert(Varchar, CID),
	@SKU  = Coalesce (@SKU  +',','') + Convert(Varchar, SKU),
	@RSID = Coalesce (@RSID +',','') + Convert(Varchar, RSID),
	@PT   = Coalesce (@PT   +',','') + Convert(Varchar, PT),
	@TID  = Coalesce (@TID  +',','') + Convert(Varchar, TID)
FROM ReleaseSequencing 
	WHERE  ',' + @Index + ','   LIKE    '%,' + Convert(Varchar,CID) + ',%' AND 
	(PLID % CID = 0) AND 
	(Released = 0) AND 
	RSID = (SELECT MIN(RSID) 
		FROM tblReleaseSequencing 
		WHERE (CID > 0) AND (PLID % CID = 0)) 
ORDER BY RSID

Note: Your original SQL translated to WHERE ... PLID % CID = 0 ... which is modulo division. Is that really what you were trying to do?
-- An example From NorthWind:


USE NorthWind

SELECT  @CID  = Coalesce (@CID  +',','') + Convert(Varchar,ProductID),
	@SKU  = Coalesce (@SKU  +',','') + Convert(Varchar,SupplierID),
	@RSID = Coalesce (@RSID +',','') + Convert(Varchar,CategoryID),
	@PT   = Coalesce (@PT   +',','') + Convert(Varchar,UnitsInStock),
	@TID  = Coalesce (@TID  +',','') + Convert(Varchar,ReorderLevel)
FROM Products 
	WHERE  patindex('%,' + Convert(Varchar,ProductID) + ',%',   ',' + @Index + ',') > 0
ORDER BY CategoryID

SELECT Cid = @CID, Sku = @SKU, Rsid = @RSID, Pt = @PT, Tid = @TID

Cid            Sku           Rsid         Pt               Tid            
-------------- ------------- ------------ ---------------- ---------------
1,4,6,8,32,10  1,2,3,3,14,4  1,2,2,2,4,8  39,53,120,6,9,31 10,0,25,0,25,0

Note: The code above does:

WHERE Patindex('%,[Every CID],%',',1,4,6,8,10,32,87,') > 0

to find CID's that occur in your @Index variable. That is about the most innefficient method of parsing a list of delimited items. See this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37704 and contained links for other alternatives. I used it here because it is the shortest and I did not want to complicate things with too many details not specifically related to the problem.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-07-21 : 14:36:42

kselvia, thanks for the input.

The problem is one of impedance mismatch, SQL versus Ladder Logic (process control language). SQL does not have arrays and Ladder Logic does not have recordsets.

There is a really good article explaining impedance mismatch at [url]http://joelonsoftware.com/[/url]. Look for the March 25th blog.

I was hoping that SQL had methods available to convert the data to a format that ladder logic could use, but alas no. Each language is specialized to a specific purpose and does not readily translate to other the languages.

The modulo statement was another example of impedance mismatch. Ladder logic is very good for bitwise manipulation, SQL is not. I was using modulo to see if a particular bit within an integer was high or low. I had not finished the logic when I realized SQL did not support arrays. At that point, I abandoned the SQL statement and tried to find a work around to the array problem.

A comma delimited list is a good solution, but unfortunately ladder logic is terrible for string manipulation. When I have more time, I may come back to this solution. In ladder logic, a string is an array of bytes. If I byte-align the result, I may have a workable solution.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-21 : 15:13:23

It is possible to dynamically declare, assign, and return variables built on the fly, but even if you do it, what will consume the results? Assuming your first example could be made to work (and it can) what is the next step considering the variables could be named like @SKU_0nnnn?

Yes it would be easy to byte-align results using the string method I suggested. From northwind again:


DECLARE @SKU Varchar(8000)
DECLARE @RSID Varchar(8000)
DECLARE @PT Varchar(8000)
DECLARE @TID Varchar(8000)
DECLARE @CID Varchar(8000)

DECLARE @SQL  VARCHAR(500)	-- SQL String
DECLARE @Index Varchar(8000)

SET @Index = '1,4,6,8,10,32,87'

SELECT  @CID  = Coalesce (@CID  +'','') + Right('000000' + Convert(Varchar,ProductID), 6),
	@SKU  = Coalesce (@SKU  +'','') + Right('000000' + Convert(Varchar,SupplierID), 6),
	@RSID = Coalesce (@RSID +'','') + Right('000000' + Convert(Varchar,CategoryID), 6),
	@PT   = Coalesce (@PT   +'','') + Right('000000' + Convert(Varchar,UnitsInStock), 6),
	@TID  = Coalesce (@TID  +'','') + Right('000000' + Convert(Varchar,ReorderLevel), 6)
FROM Products 
	WHERE  patindex('%,' + Convert(Varchar,ProductID) + ',%',   ',' + @Index + ',') > 0
ORDER BY CategoryID

SELECT Cid = @CID, Sku = @SKU, Rsid = @RSID, Pt = @PT, Tid = @TID

Cid                                     Sku                                    
--------------------------------------- -------------------------------------- 
000001000004000006000008000032000010    000001000002000003000003000014000004   

Rsid                                  Pt                                    Tid                                 
------------------------------------- ------------------------------------- ------------------------------------
000001000002000002000002000004000008  000039000053000120000006000009000031  000010000000000025000000000025000000

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-07-21 : 15:55:45

Ken, well I feel a little foolish, your solution is pretty straight forward. I will have to run some tests with it.

On the dynamic SQL, I had it working, but my program cannot access the dynamic variables. That left me having to remap the dynamic variables to explicit variables which defeated the purpose.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-21 : 16:16:31

Kenny --

how do you execute a stored procedure from Ladder Logic? How do you connect to the server, specific the command, access the return value, etc?

Just curious. Can you provide a small example? You do not use ADO at all to do this, or any COM objects?

- Jeff

Kenny Blankenship
Starting Member

25 Posts

Posted - 2004-07-21 : 16:33:40

There is a piece of software that sits between the database and the ladder logic called RSSql. It manages the mapping from the database to the ladder logic addresses. RSSql is a Rockwell Software product, its part of their industrial automation software product offering.

I do controls engineering. I am reading customer orders from the database and controlling various machinery based on the order. The particular process that this question originally pertained to deals with flow control. I have a series of conveyors delivering products to a single conveyor. The database tells me what order to release the products on to the single conveyor.

Because there are multiple intersections, each intersection has its own transaction. I was trying to consolidate the transactions into a single transaction because the basic SQL query is identical except for an index that identified the intersection and its variables. I was trying to make it more scalable too. The number of intersection can change depending upon the conveyor layout. So by altering the index loop, I could add or subtract intersections from the query very easily.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources