Click here to Skip to main content
14,635,966 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello,
Is there any way to do it something like this in Sqlite?
I want to declar a variable and append columns to it.
But Sqlite dows not allows you to use variables.

Declare @var AS VARCHAR(MAX)
SET @var = ''
SELECT   
@var =@var +  Column1 + '-' + CAST(Column2 AS VARCHAR(50))   +CHAR(10) 	
FROM TableName
SELECT @var


I searched for it but not able to find solution.. any body have come across this please help.
Posted
Updated 23-Jul-13 23:38pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

SqLite doesn't let you do that - and you aren't the first to ask. If you had googled first, you would have found a lot of questions and responses on this: Google[^] There are a couple of "workrounds" (which I'm pretty sure are all the same one): http://comments.gmane.org/gmane.comp.db.sqlite.general/62041[^]

Basically, it's a Lightweight database - it doesn't have all the features of SQL server or MySql.
You would probably be better off doing the complex stuff in your application, rather than trying to force SqLite to do something it is not designed for.
   
Comments
Amol_B 24-Jul-13 6:47am
   
Thanks
Rate this:
Please Sign up or sign in to vote.

Solution 3

There are workarounds
it could be done using in memory temp table
Create in-memory temp table for variables
BEGIN
PRAGMA temp_store = 2;
CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);

Declaring a variable
INSERT INTO [_Variables] ([Name]) VALUES ('VariableName');

Assigning a variable (pick the right storage class)
UPDATE [_Variables] SET [IntegerValue] = ... WHERE Name = 'VariableName';

Getting variable value (use within expression)
(SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1)

DROP TABLE _Variables;
END;


Reference:[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Procedures (stored or not) are not part of SQLite capabilities. So variable declaration don't work.


Note that if you run your query in SQL Server it won't work either the way you expect... I suppose you basically want your concatenation to happen " for each " row of your table. The "for each" row is not a sql notion : SQL language has been designed to think as "for all" rows. You need cursors (or other language constructs) to do "for each row" stuff...
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100