Declare local variable in Sqlite query

Question

1.44/5 (2 votes)

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 23-Jul-13 23:37pm

Amol_B

Updated 23-Jul-13 23:38pm

v2

Add a Solution

3 solutions

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:[^]

Posted 20-Sep-13 19:59pm

DuraiAmuthan

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...

Posted 23-Jul-13 23:56pm

Guirec

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2013-07-23T23:51:00

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.