Click here to Skip to main content
15,077,641 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,
Table Structure

TagNo	COLA  COLB  COLC COLD  ENABLE_COLS
1	10	     20		A,C
2		10   10   20	B,C,D


We have the above table structure.Need an insert sp to add COLUMN NAMES based on ENABLE_COLS and pass values too based on enable cols.

For EG:- for tagno 1 A,C are enabled so we need the insert statement like
SQL
INSERT INTO tbname(COLA,COLC)VALUES(10,20)
INSERT INTO tbname(COLB,COLC,COLD)VALUES(10,10,20)


What I have tried:

Kindly help to get a multiple parameter for insert statement
Posted
Updated 1-Sep-21 21:02pm
v2
Comments
OriginalGriff 30-Aug-21 7:52am
   
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

It's not entirely clear where your ENABLE_COLS value is coming from. Assuming it's a parameter or variable, something like this should work:
SQL
CREATE PROC usp_InsertValues
(
    @TagNo int,
    @EnableCols varchar(50),
    @A int,
    @B int,
    @C int,
    @D int
)
As
BEGIN
DECLARE @TestCols varchar(52);
    
    SET NOCOUNT, XACT_ABORT ON;
    
    /* Make sure the first and last column have a leading and trailing comma: */
    SET @TestCols = ',' + @EnableCols + ',';
    
    INSERT INTO tbname
    (
        TagNo,
        ENABLE_COLS,
        ColA,
        ColB,
        ColC,
        ColD
    )
    SELECT
        @TagNo,
        @EnableCols,
        CASE WHEN @TestCols Like '%,A,%' THEN @A END,
        CASE WHEN @TestCols Like '%,B,%' THEN @B END,
        CASE WHEN @TestCols Like '%,C,%' THEN @C END,
        CASE WHEN @TestCols Like '%,D,%' THEN @D END
    ;
END;
   
Comments
Maciej Los 31-Aug-21 13:08pm
   
5ed!
As already pointed out, dynamically controlling the columns to be used in an INSERT statement is a tedious task. But more importantly I would suggest rechecking the relational model you use.

Very often the kind of problem you have rises from the fact that normalization is not done properly. In such cases people tend to use columns when they actually should use rows in another table.

For example, consider a situation where you need to store user name and email addresses for the user. Let's pretend that a single user typically has one or two email addresses but the maximum is three. You could of course create a table like

User table containing columns
- name
- email1
- email2
- email3

However this kind of approach rises several problems like
- How to control saving or fetching the email addresses especially when the amount changes
- how to calculate the amount of addresses
- what if the maximum amount of addresses needs to be four or more

The correct approach would be to use two separate tables

User table containing columns
- user id
- name

Email table containing columns
- user id
- email

Now you can have as many email addresses you like without needing to make structural changes.

Since you didn't open up the actual scenario you're having, I'm of course merely guessing, but the structure and the problem you're having is awfully similar to the problem I described so if this is the case you have, I'd recommend a redesign.
   
Comments
Maciej Los 31-Aug-21 13:08pm
   
5ed!
Assuming that you want to create dynamic sql statement based on value in ENABLE_COLS column, then...
Sorry, but you're going to nowhere!

You need to know the list of columns you want to insert into!
   
VB
select case tag
case 1
     INSERT INTO tbname(COLA,COLC)VALUES(10,20)

case 2 'assume a,b and c columns are enabled.
     INSERT INTO tbname(COLA,COLB,COLC)VALUES(10,20,30)

end select
   
v2
Comments
Richard Deeming 2-Sep-21 4:55am
   
Did you try running that before you posted your answer? It's not valid SQL syntax in any DBMS I've ever seen...

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900