problem with bit datatype conversion

Hi All,

I have create following table and inserted few records

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]

I am executing following procedure to update the table.

Code Snippet

USE

---

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END

ex:

Code Snippet

exec PROC1 1,2.50,0

but I am getting following error

Code Snippet

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".

I am struck up with this query .

Please Help me regarding this issue.

Regards
Gomaz

Code Snippet

CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )

AS

BEGIN

declare @.sSQL as nvarchar(max);

SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;

exec sp_executesql @.sSQL

END

sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT

Problem with "if" statement in Stored Proc

Hi All,

I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id
IF @.industry = 31
BEGIN
AND industry = @.industry
END

The error message i get from enterprise manager is:

Error 156: Incorrect syntax near the keyword 'AND'

If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.

Thanks

What exactly are you trying to do?|||

Ok, i have a job search function, that allows the users to filter the jobs by industry. This is done with an industry dropdown list. If the value selected is "All Industries" the value passed is '31', therefore i need to ignore the industry filter and output all the results (job) regardless of what industry they are under.

I originally had the following asp.net (c#) code:

if(Request.QueryString["industry"] != "31"){
SQL += " AND industry = @.industry";
prm = new SqlParameter("@.industry",SqlDbType.VarChar,50);
prm.Direction=ParameterDirection.Input;
prm.Value = Request.QueryString["industry"];
command.Parameters.Add(prm);
}

The above worked fine, but now i'm using a stored proc (becaues i have now implemented a paging function which utilises a stored proc). I could use a "if statement" in asp.net but if i don't pass any value for @.industry, the stored proc will throw an error.

Please let me know if you require further information.

Thanks

|||

Try something like this in your stored procedure:

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id AND (industry = @.industry OR @.industry <> 31)

|||

Thanks for your response Terri,

Unfortunately this seems to have the opposite result to what i was trying to achieve. It basically returns all results no matter what industry value is passed (used to filter results), unless the value "31" has been passed. When "31" is passed it outputs nothing (this is because there aren't any records have have an industry equal to "31").

|||

Give this a shot:

SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry = (CASEWHEN @.industry = 31then industryELSE @.industryEND)

|||

This will be more efficient for you (if you only have one or two of these "ALL" type queries):

IF @.industry=31BEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idENDELSEBEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry=@.industryENDAlthough you can also do:SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND (@.industry=31ORindustry=@.industry)

Which is also more efficient, because the comparison to the field is a fixed number, SQL Server can then use indexes more efficiently -- index range vs complete index scan.

PROBLEM WIHT OPENROWSET FUNCTION

HI FRIENDS THIS IS AMIT. THE PROBLEM IS I M TRYING TO EXPORT DATA FROM SQL SERVER TABLE TO EXCEL FILE USING FOLLOWING CODE SNIPPET,

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 7.0;Database=c:\testing.xls;',
'SELECT * FROM [Sheet1$]') select * from TABLE1

BUT IT IS SHOWING SOME ERROR WHICH IS AS FOLLOWS,

Invalid object name 'OPENROWSET'.

I FOUND OPENROWSET FUNCTION IN T-SQL REFERENCE BUT STILL THE ABOVE MESSAGE IS COMING PLS HELP IN THIS MATTER ASAP.

REGARDS,

AMIT.

What version of SQL Server are you using (show the output of SELECT @.@.VERSION)?

Steve Kass
Drew University
http://www.stevekass.com|||

Make sure you have "Ad Hoc Remote Queries" enabled for your instance.

|||Kindly provide the version of the SQL Server you are using, it should ideally work without problem in SQL Server 2000 and above. You can however try the same using DTS (Data Transformation Services)|||Excel 7.0??

Try Excel 5.0 or Excel 8.0