sp problem in sql server 2008

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi friends,
I have Two tables,

DepartmentMaster fields are

SQL

[DeptId] [int] IDENTITY(1,1) NOT NULL,
    [DeptName] [varchar](250) NULL,
    [Description] [varchar](max) NULL,

And

XML

tbl_RFIDReader_M fields are
[ReaderId] [int] IDENTITY(1,1) NOT NULL,
    [ReaderName] [varchar](250) NULL,
    [OrganizationName] [varchar](250) NULL,
    [DepartmentID] [varchar](max) NULL,
    [Isdeleted] [varchar](100) NULL,</pre>

tbl_RFIDReader_M

tbl_RFIDReader_M

in gridview but i want Names Instead of id

example in

tbl_RFIDReader_M

departmentId is '1,2 ' in Display i want 'Dept1,Dept2' by sp.

so please help me to make store procedure of this.

Posted 30-Apr-14 6:46am

aarohi verma

Add a Solution

Comments

CHill60 30-Apr-14 12:52pm

So what query have you already written and where are you stuck?

1 solution

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 · Answer 1 · 2014-04-30T08:23:00

Solution 1

No.
Why not? Because it's a really, really nasty job because your database design is wrong, and needs changing - and if I help you bodge round it here, you will be back tomorrow, or next week, (and succeeding days and / or weeks) with more problems all caused by this one error.

Don't store numeric values as strings. And particularly, don't store lists of values as comma separated values in a string. And even more particularly, don't do this in an environment which (1) doesn't support CSV data; and (2) has pretty basic string handling.

Instead of storing your DepartmentIDs as a CSV string, create a new table which associates DeptID with ReaderID - so you can store each association as a separate row:

ReaderID   DeptID
   1          1
   1          2
   2          2
   2          3
  ...

That way, you can use a JOIN to convert DeptId to the name easily, and simply using the normal mechanisms. And it will make your life a lot, lot easier later on as well.

Posted 30-Apr-14 8:23am

OriginalGriff

Comments

aarohi verma 30-Apr-14 22:59pm

good suggestion but perticular row wise i done it successfully,
i made a split fuction and done a test like this

SELECT * into #temp1 FROM [DepartmentMaster]
WHERE DeptId in(select * from dbo.Split('1,2,3', ',') )

while (@countDepartment <=
select DM.Deptname into #temp2 from DepartmentMaster DM,#temp1 T
where DM.DeptId = T.DeptId

DECLARE @Deptname VARCHAR(8000)
SELECT @Deptname = COALESCE(@Deptname + ', ', '') + Deptname FROM #temp2
SELECT @Deptname

Here I get Dept1,Dept2,Dept3

but not display whole table yar.

OriginalGriff 1-May-14 1:49am

I didn;t say you *couldn't* do it - just that it's a very poor idea: because every time you want to access that column, you have to do code roughly like what you have. Which means that when you want to (for example) select "all the readers in 'Department 4'" you have to create a temporary table for *every single row* to see if the reader is in the department.
And you have to do that when you want to do almost anything with that column!
This is spectacularly inefficient, and evidence of poor database design.

Yes, it makes one job easy - inserting the data in the first place - but it makes everything else much harder, and less reliable. Just becasue you can do somethign doesn;t mean it is a good idea!

Think of it this way: yes, it is possible to drive your car when you are way over the legal limit for alcohol - you might not crash every time - but that doesn't mean that "drunk driving" should be part of your driving test!

aarohi verma 1-May-14 1:57am

ya you're right but i cant change it, i have to do this way.

OriginalGriff 1-May-14 2:23am

No you don't - it's not as if this was a real-world application, it's just your homework.
You are just trying to avoid going back and changing existing code and hoping tehre is a simple way round it that doesn't involve the nasty piece of SQL you have above each time you want to use the data.
And there isn't.
So add the table, knock up a quick bit of software to convert the column to rows, and change your software to use the new table. It will be a lot, lot quicker in the long run than trying to faff round a poor design choice. Trust me - I've been there! :laugh:

aarohi verma 1-May-14 7:14am

thank done

OriginalGriff 1-May-14 8:24am

You're welcome!