Sql multiple parameter for insert query

Question

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 30-Aug-21 1:20am

Member 11658469

Updated 1-Sep-21 21:02pm

Maciej Los

v2

Add a Solution

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.

4 solutions

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)

Richard Deeming · Answer 1 · 2021-08-30T23:31:00

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;

Wendelius · Answer 2 · 2021-08-31T05:13:00

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.

Maciej Los · Answer 3 · 2021-08-30T08:40:00

Solution 1

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!

Posted 30-Aug-21 8:40am

Maciej Los

kgmmurugesh · Answer 4 · 2021-09-01T21:02:00

Solution 4

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

Posted 1-Sep-21 21:02pm

kgmmurugesh

Updated 1-Sep-21 21:03pm

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

Sql multiple parameter for insert query

4 solutions

Solution 2

Solution 3

Solution 1

Solution 4

Add your solution here

Preview 0