Simple way to creating the Store procedure parameters for the table in SQL Server

Simple way to creating the Store procedure parameters for the table in SQL Server

Step 1: Pass the Table name as parameter for the below query and execute

Using the code

//

// code

//

declare @TableName sysname = '@TableName'

declare @result varchar(max) = ''

SELECT

       @result = @result + '

@' + ColumnName + ' ' + ColumnType + ''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + ') =NULL,'

              ELSE typ.name + '=NULL,'

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result

print @result

Read More »

SQL Server Split Function

SQL Server Split Function

Split the input string and convert to table in SQL Server

Introduction

This article explains you about the SQL function to get the table format while passing the comma separator string

Using the code

Copy the below code and page in the SQL editor

--//

--// Main Function

--//

CREATE FUNCTION dbo.Split

(

    @Line nvarchar(MAX),

    @SplitOn nvarchar(5) = ','

)

RETURNS @RtnValue table

(

    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    Data nvarchar(100) NOT NULL

)

AS

BEGIN

    IF @Line IS NULL RETURN

    DECLARE @split_on_len INT = LEN(@SplitOn)

    DECLARE @start_at INT = 1

    DECLARE @end_at INT

    DECLARE @data_len INT

    WHILE 1=1

    BEGIN

SET @end_at = CHARINDEX(@SplitOn, @Line, @start_at)

SET @data_len =

                           CASE @end_at

                                  WHEN 0 THEN LEN(@Line)

                                  ELSE @end_at - @start_at

                           END

INSERT INTO @RtnValue (data)

       VALUES (SUBSTRING(@Line, @start_at, @data_len));

IF @end_at = 0 BREAK;

SET @start_at = @end_at + @split_on_len

END

RETURN

END

Steps to check the function execution

select * from dbo.split('S,D,U,V',',')

OutPut:-

	ID	Data
1	1	S
2	2	D
3	3	U
4	4	V

Read More »