How to make array in sql 2008

Question

0.00/5 (No votes)

See more:

i have make a small array and it's work fine no problem
but win i put this array in query or sub query it's not work
give me no error just give me null value

SQL

DECLARE @EmployeeList nvarchar(max)= '';
(SELECT @EmployeeList = @EmployeeList + ',' + CAST(AccountsDefinition.[ID] AS nvarchar(max)) FROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])

SQL

SELECT @EmployeeList

SQL

,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157

the sub query

SQL

SELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 FROM AzenSarfKhazna WHERE AzenSarfKhazna.[OPID] = '1' AND AzenSarfKhazna.[Type] = 'Other' AND AzenSarfKhazna.[TypeID] IN (SELECT @EmployeeList)

What I have tried:

help me please in this error i have bean a long time search for solution

Posted 8-Mar-16 2:44am

Mostafa Salama S

Updated 8-Mar-16 3:13am

Add a Solution

2 solutions

Solution 2

Your 'Array' isn't an array or set. It is a nvarchar. You know, this thing: DECLARE @EmployeeList nvarchar(max)= '';

Don't use the word 'Array'. They don't exist in sql. You need a table, table variable, temp table or common table expression (CTE).

psst. Don't use table or temp table. You don't need them.

Table Variable:

SQL

declare @EmployeeList as table(id int not null)
insert into @EmployeeList 
select AccountsDefinition.[ID]
FROM AccountsDefinition,Directexpenses 
WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID]

CTE:

SQL

with EmployeeList as ( 
select AccountsDefinition.[ID] as id
FROM AccountsDefinition,Directexpenses 
WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID]
)

then just use a join

SQL

SELECT 
SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 
FROM AzenSarfKhazna 
INNER JOIN @EmployeeList on AzenSarfKhazna.TypeID = EmployeeList.id
WHERE AzenSarfKhazna.[OPID] = '1' 
AND AzenSarfKhazna.[Type] = 'Other'

Or, the correct way is to just use the table in the same query:

SQL

SELECT 
SUM(ISNULL(a1.[KhaznaTotal],0)) AS Expr4 
FROM AzenSarfKhazna as a1
INNER JOIN AzenSarfKhazna as a2 on a1.TypeID = a2.ID
WHERE AzenSarfKhazna.[OPID] = '1' 
AND a1.[Type] = 'Other' 
AND a2.[HaveFather] = Directexpenses.[DEXID]

Hope that helps
Andy

Posted 8-Mar-16 3:13am

Andy Lanng

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)

Dave Kreskowiak · Accepted Answer · 2016-03-08T03:09:00

It doesn't work because when you created "your array" you didn't create an array. There isn't any such concept in SQL. What you did was create a single string of text.

Now, what your subquery is doing in its WHERE clause is comparing a "TypeID" value to this string. Unless your TypeID value is a string that matches ",135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157" exactly, this is not going to work.

You don't create a string, or what you're calling an "array". You can combine these queries into a single query:

SELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4
FROM AzenSarfKhazna
WHERE AzenSarfKhazna.[OPID] = '1'
    AND AzenSarfKhazna.[Type] = 'Other'
    AND AzenSarfKhazna.[TypeID] IN (SELECT AccountsDefinition.[ID]
        FROM AccountsDefinition,Directexpenses
        WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])