All geek questions in one place

How to populate a column of a result view with a value from stored code in SQL?

Edit: HAS to be saved by proc, NOT a function , sorry!

I have a table:

TABLE: dbo.Numbers 
Number_ID | Number
1         | 0
2         | 1
3         | 2
4         | 3
5         | 4
6         | 5
7         | 6     
8         | 7

I need the following output ( as a view ):

Number_ID | ModifiedNumber
1         | lol the num is 0
2         | lol the num is 1
3         | lol the num is 2
4         | lol the num is 3
5         | lol the num is 4
6         | lol the num is 5
7         | lol the num is 6      
8         | lol the num is 7

I have a stored procedure for this:

CREATE PROCEDURE dbo.UselessStoredProc @inputNum int
AS
SELECT 'lol my number is: ' + CONVERT(varchar(max), @inputNum)
GO

--TEST
EXEC dbo.UselessStoredProc @inputNum=2;

My ultimate goal is to populate ModifiedNumbercol through a saved process, for example:

SELECT Number_ID, EXEC UselessStoredProc @inputNum = Number_ID  as ModifiedNumber
FROM [TestDb].[dbo].[Numbers]

Obviously this does not work. How to do it.

PS Please don't tell me "just:

SELECT Number_ID, 'lol my number is: ' + CONVERT(varchar(max), Number_ID) as ModifiedNumber
FROM [TestDb].[dbo].[Numbers]

"

I know well that I can do this - this is obviously an example, the real code is much more complicated and requires a MEMORED PROCEDURE . Note that I intentionally return a string from a sample stored procedure - I need complex values, not just that int.

Edit:

SQL Server 2012

. . "" , .

2: , SP, , , , SQL. , , , :

DECLARE @sql nvarchar(max) = 'SELECT @Data_Table_Key = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + QUOTENAME(CONSTRAINT_NAME)), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ''' + @Data_Table_Name + ''''
EXECUTE sp_executesql @sql, N'@Data_Table_Key varchar(200) OUTPUT', @Data_Table_Key OUTPUT

Edit: , -, openrowset, , . , 5 , . . , , , , .

+4
5

2: , SP, , , , SQL. , , , :

DECLARE @sql nvarchar(max) = 'SELECT @Data_Table_Key = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + QUOTENAME(CONSTRAINT_NAME)), ''IsPrimaryKey'') = 1 AND TABLE_NAME = ''' + @Data_Table_Name + ''''
EXECUTE sp_executesql @sql, N'@Data_Table_Key varchar(200) OUTPUT', @Data_Table_Key OUTPUT

. SQL.

:

SELECT @Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name

@Data_Table_Key.

, "" PK . , , ( , !).

:

CREATE TABLE t_test_multiple_pk_fields ( key1 int NOT NULL,
                                         key2 int NOT NULL,
                                            constraint pk_test PRIMARY KEY (key1, key2),
                                         value1 int,
                                         value2 int)

DECLARE @Data_Table_Name sysname,
        @Data_Table_Key  sysname

SELECT @Data_Table_Name = 't_test_multiple_pk_fields'

SELECT Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name 

SELECT @Data_Table_Key = COLUMN_NAME 
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
 WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 
  AND TABLE_NAME = @Data_Table_Name 

SELECT @Data_Table_Key
+4

RBAR , , SQL Server , , . , , :

-- Generate some dummy data

Declare @Numbers Table
(
    Number_ID int primary key,
    Number varchar(100)
)

;with cte as
(
    select 1 [Value]
    union all
    select value+1 from cte where (value+1) < 9
)
insert into @Numbers(Number_ID, number)
select value, convert(varchar(100), value-1) from cte

-- Process the records, one at a time

declare @id int=0
declare @result table (value varchar(100))
while (1=1)
begin
    -- Get the ID of the next record for processing
    select @id=min(number_id) from @numbers where number_id>@id
    if (@id is null)
        break

    -- Call the useless stored procedure and dump the results into a temp table (table layout must match that of the resultset from the SP)
    insert into @result(value)
    exec UselessStoredProc @id

    -- Copy the value from the temp table to the appropriate record in your numbers table
    update n set Number=r.value
    from @numbers n
    cross join @result r
    where n.Number_ID=@id

    -- Clear out the temp table, ready for your next value
    delete from @result
end

-- Show the results
select * from @Numbers

WHILE , , ( , ), CURSOR.

, - RBAR, .

+2

, , .

udtNumbers. . RBAR

create type udtNumbers(Number_ID int, Number int)

.

Alter PROCEDURE dbo.UselessStoredProc @inputNum udtNumbers READONLY
AS
SELECT Number_ID, 'lol my number is: ' + CONVERT(varchar(max), Number)
from @inputNum
GO


Declare @inputNum udtNumbers

insert into @inputNum(Number_ID, Number)
select Number_ID, Number
from dbo.Numbers

exec dbo.UselessStoredProc @inputNum

, - .

+1

. , , , , 1. , , . ( AdventureWorks)...

DROP FUNCTION IF EXISTS dbo.UselessFunction1 
go
CREATE FUNCTION dbo.UselessFunction1(@inputnum int)
RETURNS varchar(30)
AS
BEGIN
    DECLARE @outputString varchar(30)
    SELECT @outputString = 'lol my number is  ' + CAST(@inputnum-1 AS varchar(10))
    RETURN @outputString
END
GO
DROP VIEW IF EXISTS dbo.UselessView1
go
CREATE VIEW dbo.UselessView1 AS
SELECT Name,dbo.UselessFunction1(pc.ProductCategoryID) AS Number_ID
FROM Production.ProductCategory AS pc
GO
SELECT * FROM dbo.UselessView1 AS uv;

, , , , , , UselessStoredProc, Value Inline Table Value , ....

DROP FUNCTION IF EXISTS dbo.UselessFunction2
go 
CREATE FUNCTION dbo.UselessFunction2(@inputnum int)
RETURNS TABLE
AS
RETURN (
        SELECT 'lol my number is  ' + CAST(@inputnum-1 AS varchar(10)) AS Number_ID
        )
GO
DROP VIEW IF EXISTS dbo.UselessView2
go
CREATE VIEW dbo.UselessView2 AS
SELECT Name,x.Number_ID
FROM Production.ProductCategory AS pc
CROSS APPLY (SELECT Number_ID FROM dbo.UselessFunction2(pc.ProductCategoryID)) x
GO
SELECT * FROM dbo.UselessView1 AS uv;
+1

, "" , , .

. -

CREATE TABLE t_tableA (keyA int NOT NULL PRIMARY KEY, valueA1 varchar(10), valueA2 datetime)
CREATE TABLE t_tableB (keyB int NOT NULL PRIMARY KEY, valueB1 int, valueB2 varchar(500), valueB3 int)

GO
CREATE VIEW v_all_tables
AS
SELECT table_name = 't_tableA', table_key = keyA, table_value = Convert(nvarchar(max), valueA1) + ' - ' + Convert(nvarchar(max), valueA2)
  FROM t_tableA

UNION ALL 

SELECT table_name = 't_tableB', table_key = keyB, table_value = '[' + Convert(nvarchar(max), valueB1) + '] ' + Convert(nvarchar(max), valueB2) + ' = ' + Convert(nvarchar(max), valueB3)
  FROM t_tableB

GO

SELECT * FROM v_all_tables WHERE table_name = 't_tableA'

, " ", . . , , , ! , , , , / , .

+1

Source: https://habr.com/ru/post/1666509/

More articles:


All Articles