在存储过程中传递值 [英] passing a value in stored proc

问题描述

大家好，

我有一个表格Test，该表格包含名称，年龄和薪水三列.

我想查询为

从测试订单中按名称选择*
从年龄的测试顺序中选择*
从薪水的测试订单中选择*

我想通过存储过程来使用它，这意味着我想从
传递一个值
在SP中的参数中按名称排序，按年龄排序，按工资排序.

这样，如果我按名称传递订单，那么我在SP中的查询应该是
从测试订单中按名称选择*.

我创建SP时遇到了错误.
请帮忙

Hi all,

I have a table Test having three columns as Name, age and salary.

I want query as

select * from Test order by Name
select * from Test order by age
select * from Test order by salary

I want to use it using stored procedure means I want to pass one values from

order by Name ,order by age,order by salary in a parameter in SP.

Such that if I pass order by Name then I my query in SP should be
select * from Test order by Name.

I created SP as but getting error.
Please help

create proc Tesstify
@string varchar(max)
as
declare
@abcc varchar(max)
set @abcc=@string
select * from abc @abcc

Mohd Wasif

Mohd Wasif

推荐答案

如果您需要基础知识方面的帮助，则可以查看此链接 [^ ].

If you need help with basics, you can see this link[^].

请尝试使用动态SQL

Please try Dynamic SQL

DECLARE @tmp AS TABLE ( Name VARCHAR(10), Age INT, Salary INT)
INSERT INTO @tmp(Name,Age,Salary) (SELECT 'A',1,10)
INSERT INTO @tmp(Name,Age,Salary) (SELECT 'C',10,11)
INSERT INTO @tmp(Name,Age,Salary) (SELECT 'B',100,12)
DECLARE @DynamicSQL as VARCHAR(100)
DECLARE @YourParameter VARCHAR(10)
SET @YourParameter =1
SET @DynamicSQL = 'SELECT * FROM @tmp ORDER BY ' + @YourParameter
Exec(@DynamicSQL)

在SQL Server中执行动态SQL命令

Execute Dynamic SQL commands in SQL Server

这篇关于在存储过程中传递值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！