Sql Queries

Transact-SQL
1

WhatsApp Image 2020-09-15 at 1.41.49 AM
WhatsApp Image 2020-09-15 at 1.41.49 AM1600×900 113 KB

2

Welcome to the forum,

please provide sample data as DDL and DML meaning

create table #krishh001(effectiveDate date, expirationdate date, LOB varchar(50), Product varchar(50) ) --etc providing all of the columns and data type

--then please provide sample data to work with

insert into #t-sql
select '2020-01-01', '2020-12-31', --etc

another option is to give us full control of your sql server.

3

Sure...gimme some time

4

I need to write a procedure so that when I have to update the table for
more than one Product Type at once, I only have to plug in the table
name and the multiple values and it generates an insert script :

CREATE PROCEDURE dbo.INS_MULTIPLE_PRODUCT_TYPE
@pstrSchemaName
VARCHAR(200)
,@pstrTable
VARCHAR(200)
@pstrColumnValues
VARCHAR(MAX) -- comma separated column values to
be inserted are given in the correct orderAS
BEGIN
DECLARE @index int,
@INSQUERY VARCHAR(MAX),
@Delimiter VARCHAR(2) = ',',
@Total_Rows INT,
@Counter INT = 1,
@productType VARCHAR(255),
@InputText VARCHAR(MAX),
@ColumnValuesText VARCHAR(MAX) = ''
DECLARE @OutputTable TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
SET @index = -1;
SET @InputText= @pstrColumnValues
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTable VALUES (@InputText)BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTable VALUES (LEFT(@InputText, @index - 1))
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
ELSE
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
SELECT @Total_Rows= Count(1) FROM @OutputTable
WHILE (@Counter<=@Total_Rows )
BEGIN

SELECT @productType = val
FROM @OutputTable
WHERE pos=@Counter
IF @ColumnValuesText = ''
BEGIN
SET @ColumnValuesText = ''''+@productType+''''
END
ELSE
BEGIN
SET @ColumnValuesText = @ColumnValuesText + ',' +
''''+@productType+''''
END
SET @Counter=@Counter+1
End
SELECT @INSQUERY= '''INSERT INTO '+@pstrSchemaName+'.'+@pstrTable +'
Values( '+ @ColumnValuesText+ ')'
SELECT @INSQUERY AS 'INSERT QUERY'
print(@INSQUERY)
END

  1. I need to modify my procedure so that it takes the names of columns
    that have multiple values and generates an insert script. :

ALTER PROCEDURE dbo.INS_MULTIPLE_PRODUCT_TYPE
@pstrSchemaName
VARCHAR(200)
,@pstrTable
VARCHAR(200)
,@pstrColumnNames
VARCHAR(MAX) -- comma separated column names
,@pstrColumnValues
VARCHAR(MAX) -- comma separated column values to
be inserted are given in the correct order

AS
BEGIN
DECLARE @index int,
@INSQUERY VARCHAR(MAX),
@Delimiter VARCHAR(2) = ',',
@Total_Rows INT,
@Total_Rows_Columns INT,
@Counter INT = 1,
@productType VARCHAR(255),
@InputText VARCHAR(MAX),
@ColumnValuesText VARCHAR(MAX) = '',
@ColumnNamesText VARCHAR(MAX) = ''
DECLARE @OutputTable TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
DECLARE @OutputTableColumns TABLE
(
pos INT IDENTITY PRIMARY KEY,
val VARCHAR(4000)
)
SET @index = -1;
SET @InputText= @pstrColumnValues
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTable VALUES (@InputText)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTable VALUES (LEFT(@InputText, @index - 1))
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
ELSE
SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
END
SELECT @Total_Rows= Count(1) FROM @OutputTable
WHILE (@Counter<=@Total_Rows )
BEGIN

SELECT @productType = val
FROM @OutputTable
WHERE pos=@Counter
IF @ColumnValuesText = ''
BEGIN
SET @ColumnValuesText = ''''+@productType+''''
END
ELSE
BEGIN
SET @ColumnValuesText = @ColumnValuesText + ',' +
''''+@productType+''''
END
SET @Counter=@Counter+1
End
SET @index = -1;
SET @InputText= @pstrColumnNames;
SET @Counter = 1;
WHILE (LEN(@InputText) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @InputText)
IF (@index = 0) AND (LEN(@InputText) > 0)
BEGIN
INSERT INTO @OutputTableColumns VALUES (@InputText)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @OutputTableColumns VALUES (LEFT(@InputText, @index

  • 1))
    SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
    ELSE
    SET @InputText = RIGHT(@InputText, (LEN(@InputText) - @index))
    END
    Select @Total_Rows_Columns = Count(1) FROM @OutputTableColumns
    WHILE (@Counter<=@Total_Rows_Columns )
    BEGIN

SELECT @productType = val
FROM @OutputTableColumns
WHERE pos=@Counter
IF @ColumnNamesText = ''
BEGIN
SET @ColumnNamesText = @productType
END
ELSE
BEGIN
SET @ColumnNamesText = @ColumnNamesText + ',' +
@productType
END
SET @Counter=@Counter+1
End
SELECT @INSQUERY= '''INSERT INTO '+@pstrSchemaName+'.'+@pstrTable+
'('+@ColumnNamesText+')' +' Values( '+ @ColumnValuesText+ ')'
SELECT @INSQUERY AS 'INSERT QUERY'
print(@INSQUERY)
END

5

Ok, why are you doing dynamic query and while loops. You can avoid both.

Let us know when you have sample ddl and dml

1 Like