Comment remplir une colonne de la vue des résultats avec la valeur d'une procédure stockée en SQL ?

Question

Edit : doit être une procédure stockée, PAS une fonction Désolé !

J'ai une table :

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

Je veux la sortie suivante ( comme une vue ) :

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

J'ai une procédure stockée pour faire cela :

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

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

Mon objectif final est de remplir le ModifiedNumber col via une procédure stockée, par exemple :

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

De toute évidence, cela ne fonctionne pas. Comment puis-je accomplir ceci.

P.S. S'il te plaît, ne me dis pas "fais-le" :

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

"

Je sais très bien que je peux le faire - il s'agit bien évidemment d'un exemple, le vrai code est beaucoup plus compliqué et nécessite une PROCÉDURE STOCKÉE . Notez que j'ai intentionnellement renvoyé une chaîne de caractères à partir de l'exemple de procédure stockée - j'ai besoin de valeurs complexes, pas seulement de int .

Edit :

SQL Server 2012

Les fonctions ne sont PAS une option. Je m'excuse pour la confusion. Ma "fonction" doit appeler des procédures stockées intégrées, et les fonctions ne peuvent pas le faire.

Edit 2 : La raison pour laquelle j'ai besoin d'un SP, plutôt que d'une fonction, est que j'ai besoin de trouver dynamiquement la clé primaire d'une table, et donc d'utiliser du SQL dynamique. Je pensais que c'était trop d'informations, mais il semble que ce soit nécessaire :

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 : En fin de compte, j'essayais de faire quelque chose qui ne peut être fait avec openrowset ce qui est un sale, sale hack. J'ai fini par écrire une procédure stockée qui écrit dans une table toutes les 5 minutes, et le logiciel tiers utilise cette table. J'apprécie toute l'aide apportée. J'ai accepté la réponse qui m'a le plus aidé, mais la réponse de la boucle while a également été utile et m'a permis d'obtenir ce dont j'ai besoin, mais avec des performances inacceptables.

Answer 1

Sur la base de

Edit 2 : La raison pour laquelle j'ai besoin d'un SP, plutôt que d'une fonction, est que j'ai besoin de trouver dynamiquement la clé primaire d'une table, et donc d'utiliser du SQL dynamique. Je pensais que c'était trop d'informations, mais il semble que ce soit nécessaire :

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

Je suis confus. Pourquoi faire ça en SQL dynamique en premier lieu.

Vous pouvez tout aussi bien le faire :

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 

et aboutir au même résultat dans votre @Data_Table_Key variable.

Cela dit, votre approche vous donnera des résultats "incomplets" si vous essayez d'extraire les colonnes PK d'une table dont la clé primaire comporte plusieurs champs. Il est impossible de dire lequel vous obtiendrez, mais ce sera l'un d'entre eux (généralement le dernier, mais vous ne pouvez pas y compter !).

Quelques exemples de code :

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

Answer 2

Traitement de vos données RBAR est à éviter autant que possible car SQL Server est conçu pour le traitement par lots, mais je pars du principe que vous n'avez pas le choix en la matière. En gardant cette hypothèse à l'esprit, cela devrait vous permettre d'obtenir ce que vous recherchez :

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

J'utilise ici une boucle WHILE pour parcourir votre tableau de chiffres, car elle est beaucoup plus efficace (ou devrais-je dire moins inefficace) qu'un CURSOR.

Encore une fois, s'il y a un moyen d'éviter le schéma RBAR, je le ferais.

Answer 3

Votre question n'a aucun sens, juste pour montrer une option, voici une façon d'utiliser type de table paramètre.

créer un type de table appelé udtNumbers . Ceci sera utilisé pour passer les nombres comme paramètre d'entrée à la procédure. Le plus important est d'éviter RBAR

create type udtNumbers(Number_ID int, Number int)

modifier la procédure pour accepter le type de table comme paramètre d'entrée.

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

appelez maintenant la procédure

Declare @inputNum udtNumbers

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

exec dbo.UselessStoredProc @inputNum 

J'espère que cela vous aidera d'une manière ou d'une autre à résoudre votre problème concret.

Answer 4

Les fonctions peuvent certainement être utilisées dans les vues. Vous ne précisez pas comment le nombre doit être modifié, si ce n'est en l'affichant décrémenté de 1. Ceci étant dit, la fonction doit être capable d'accomplir la logique que vous souhaitez. De manière simplifiée, ce serait donc (en utilisant les tables 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;

Bien entendu, les fonctions scalaires ne sont pas idéales du point de vue des performances. Si vous pouvez vous contenter d'une seule requête pour exécuter la logique exécutée par UselessStoredProc, une fonction en ligne à valeur de tableau serait préférable, par exemple .....

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;

Answer 5

Une autre approche consisterait à créer une vue "gigantesque" en amont qui combine toutes les tables que l'application pourrait vouloir voir.

Par exemple, quelque chose du genre

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'

Il s'agit d'une vue, elle serait "en temps réel" et vous pouvez facilement filtrer la table dont vous avez besoin. Cela pourrait fonctionner pour des centaines de tableaux. Cependant, chaque fois que vous ajoutez une nouvelle table à la base de données, vous devez mettre à jour la vue pour l'inclure ! Au lieu d'écrire une procédure stockée qui récupère les données, vous pourriez alors écrire une procédure stockée qui dépose/crée la vue et l'exécute chaque nuit ou chaque fois qu'une nouvelle table est ajoutée.