How to convert Sql query to stored procedure

vak.love

SOLVED CLOSED ACTIVE ANSWERED
User: vak.love
Posted: on Jan 30, 2014 09:13 AM
Forum: SQL Server
Answer: 1
Views: 2178

Hi all

I need to help to convert sql query to stored procedure .

I have page that has differenet option below image .when we check those option we get the data .

Here is my sql string in .net i want to convert into stroed procedure so that i can improve perfomance .

string sqlString = "SELECT DISTINCT nvg.ltypeid, SLenses.DESCRIPTION as 'Product Name', SLenses.TYPE, SLenses.WEBPRICE,SLenses.NewProductInd FROM nvg JOIN SLenses ON nvg.pid = SLenses.pid LEFT OUTER JOIN nvgo on nvgo.pid=SLenses.pid ";
    
                            if (type != "")
                            {
                                sqlString = sqlString + "WHERE SLenses.TYPE IN (" + type + ")";
                            }

                            if (lens != "" && type != "")
                            {
                                sqlString = sqlString + " AND SLenses.MATERIAL IN (" + lens + ")";
                            }
                            else if (lens != "")
                            {
                                sqlString = sqlString + "WHERE SLenses.MATERIAL IN (" + lens + ")";
                            }

                            if (option != "" && (lens != "" || type != ""))
                            {
                                sqlString = sqlString + " AND nvgo.[OPTIONS] IN (" + option + ")";
                            }
                          else if (option != "")
                            {
                                sqlString = sqlString + "WHERE nvgo.[OPTIONS] IN (" + option + ")";
                            }

                            if (brand != "" && (option != "" || lens != "" || type != ""))
                            {
                                sqlString = sqlString + " AND nvg.manid IN (" + brand + ")";
                            }
                            else if (brand != "")
                            {
                                sqlString = sqlString + "WHERE nvg.manid IN (" + brand + ")";
                            }

Thank you Guys.

Download FREE API for Word, Excel and PDF in ASP.Net

Azim

0 3

Reply
ANSWER
User: Azim
Replied: on Jan 31, 2014 04:58 AM
Report

This Way

CREATE PROCEDURE Sp_Query
	@SqlQuery VARCHAR(MAX),
	@Type VARCHAR(50),
	@Lens VARCHAR(50),
	@Option VARCHAR(50),
	@Brand VARCHAR(50)
AS
BEGIN	
	SET NOCOUNT ON;

    IF @Type <> ''
	SET @SqlQuery = @SqlQuery + 'WHERE SLenses.TYPE IN ' + '(' +@Type +')'
    
    IF @Lens <> '' AND @Type <> ''
	SET @SqlQuery = @SqlQuery + 'AND SLenses.MATERIAL IN ' + '(' +@Lens +')'
   
    IF @Lens <> '' 
	SET @SqlQuery = @SqlQuery + 'WHERE SLenses.MATERIAL IN ' + '(' +@Lens +')'
    
    IF @Option <> ''  AND @Lens <> NULL  OR @Type <> '' 
	SET @SqlQuery = @SqlQuery + 'AND nvgo.[OPTIONS] IN ' +'(' +@Option +')'
    
    IF @Option <> '' 
	SET  @SqlQuery = @SqlQuery + 'WHERE nvgo.[OPTIONS] IN ' +'(' +@Option +')'
     
    IF @Brand <> ''  AND @Option <> ''  OR @Lens <> ''  OR @Type <> '' 
    SET @SqlQuery = @SqlQuery +  'AND nvg.manid IN ' + '('+ @Brand+ ')'
     
    IF @Brand <> ''
	SET @SqlQuery = @SqlQuery + 'WHERE nvg.manid IN' + '('+ @Brand + ')'
	
	EXEC @SqlQuery
END
GO

Thank You

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

How to convert Sql query to stored procedure

Company

Explore

Follow us

Contact Us