CODEWITHSUNDEEP
sqlsql-serveroracle-databasestored-procedures
Luca Martini
Luca Martini

Reputation: 1474

%Rowtype equivalent in SQL Server

I'm about to convert a stored procedure from pl/sql to SQL Server. The procedure uses a cursor to loop through the results of a select query. Is there a SQL Server equivalent to the ORACLE rowtype construct?

Upvotes: 15

Views: 29091

Answers (4)

Mano Mangaldas
Mano Mangaldas

Reputation: 75

you can create pass JSON String as the parameter to the SP .. this behaves similar to ROWTYPE

CREATE PROCEDURE [dbo].[SPInsertPerson]

(

@JsonData NVARCHAR(MAX)

)

AS

BEGIN

SET NOCOUNT ON

INSERT INTO [dbo].[Person] (
    [Name], 
    [DOB])
SELECT
    JSON_VALUE(@JsonData, '$.personname') AS Name,
    TRY_CAST(JSON_VALUE(@JsonData, '$.dob') AS DATE) AS DOB;

END

Upvotes: 0

Piotr Grudzień
Piotr Grudzień

Reputation: 189

As @HarveyFrench responded there is no @rowtype. To generate all columns variables You can use this select and copy paste from results:

SELECT 'DECLARE @customPrefix' + COLUMN_NAME  + '  ' +  DATA_TYPE + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
TABLE_NAME = 'Your_table'

Upvotes: 5

HarveyFrench
HarveyFrench

Reputation: 4568

This is a massive reason to dislike SQL Server when compared to oracle.

I am so disappointed that SS doesn't have %TYPE and %ROWTYPE. These save hours of work when initially writing code and in the event that the type of a column needs to change, it saves having to go back and re-work all the code.

In Oracle I used to write something like:

DECLARE @MyRowVar AS ATable%ROWTYPE;

In SQL Server, I just had to write this this:

DECLARE @External_ID                AS BIGINT = NULL;
DECLARE @PlatformID                 AS INT = NULL;
DECLARE @ActorIDOfReseller          AS INT = NULL;
DECLARE @ActorIDOfClient            AS INT = NULL;
DECLARE @ActorIDOfExtension         AS INT = NULL;
DECLARE @CallType                   AS NCHAR (10) = NULL;
DECLARE @CallInitiatedDate          AS DATE = NULL;
DECLARE @CallInitiatedTimeHH24MI    AS TIME (0) = NULL;
DECLARE @TimePeriodID               AS INT = NULL;
DECLARE @CallAnswered               AS DATETIME = NULL;
DECLARE @CallAnsweredYN             AS BIT = NULL;
DECLARE @CallDispositionID          AS INT = NULL;
DECLARE @CountryID                  AS INT = NULL;
DECLARE @CallPrefixID               AS INT = NULL;
DECLARE @FromNumber                 AS VARCHAR (32) = NULL;
DECLARE @ToNumber                   AS VARCHAR (80) = NULL;
DECLARE @CallDuration               AS INT = NULL;
DECLARE @CallCostToExtension        AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToClient           AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToReseller         AS DECIMAL (10, 6) = NULL;
DECLARE @CallCostToAdmin            AS DECIMAL (10, 6) = NULL;
DECLARE @Flow                       AS VARCHAR (3) = NULL;
DECLARE @CallStart                  AS DATETIME = NULL;
DECLARE @MoneyUnit                  AS VARCHAR (32) = NULL;
DECLARE @Prefix                     AS VARCHAR (32) = NULL;
DECLARE @External_CallID            AS VARCHAR (255) = NULL;

This is making me very sad.

Harvey

Upvotes: 33

keith
keith

Reputation: 3110

The SQL way of using a cursor is shown below

DECLARE @colA varchar(50), @colB varchar(50)

DECLARE myCursor CURSOR FOR
Select columnA, columnB From table

OPEN myCursor

FETCH NEXT FROM myCursor INTO @colA, @colB

WHILE @@FETCH_STATUS = 0
BEGIN

--do something with @colA and @colB

FETCH NEXT FROM myCursor INTO @colA, @colB

END

CLOSE myCursor
DEALLOCATE myCursor

reference link

Upvotes: 8

Related Questions