动态SQL搜索 [英] Dynamic SQL for search

问题描述

你好！


我需要开发一个动态存储过程来在表列中搜索项目

我已经开发了一个静态SQL存储过程.
如下:

Hello!


I need to develop a dynamic stored procedure to search for an item into table columns

I have developed a Static SQL Stored Procedure.
It is as follows:

CREATE PROCEDURE [dbo].[spUserInfo_SearchSp]
(
    @SearchItem varchar(50),<pre></pre>
    @SearchBy varchar(50)
)
AS
BEGIN
    SET NOCOUNT ON;
    
    Select 
        UserName, FirstName, LastName, EmailId, IsActive
    From UserInfo
    where 
       (@SearchBy=''UserName'' AND UserName like ''%''+@SearchItem+''%'')
    OR (@SearchBy=''FirstName'' AND  FirstName like ''%''+@SearchItem+''%'')
    OR (@SearchBy=''LastName'' AND LastName like ''%''+@SearchItem+''%'')
    OR (@SearchBy=''EmailId'' AND EmailId like ''%''+@SearchItem+''%'');
        
END

效果很好.
但是我需要使用动态SQL进行相同的操作.
有人可以帮我吗?

It works just fine.
But I need the same thing be done with Dynamic SQL.
Can anybody help me with this?

推荐答案

我不明白.您已经有一个存储过程.它需要几个输入参数才能返回一些数据.

现在，您只需在运行时通过代码为这些输入参数赋予一个值，然后调用此存储过程即可.

我认为您无需根据需要创建动态存储过程.您只需要一个具有不同参数值的静态变量即可.

I don''t get it. You already have a Stored Procedure in place. It takes couple of input parameters in order to return back some data.

What you need now is simply give these input parameters a value at runtime through your code and call this stored procedure.

I don''t think you need to create a dynamic stored procedure for your requirement. A static one with different parameter values is all you need.

Hello Sandeep，
感谢您的答复.

但是我想在这里使用动态SQL.
在学习动态SQL时，我找不到方法怎么做?
你能帮我实现吗?

Hello Sandeep,
Thanks for ur reply.

But I want to use dynamic SQL here.
As I m learning dynamic SQL, I can''t find way how to do it?
Can u help me to achive it?

这篇关于动态SQL搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！