How to get the numbers of row in a table using function. Simple but there is a problem i have

Here is the code. Trying to get the number of the rows in a table. If it is greater than 0 return false else return true. Later i changed this code with if else but i am wondering why it is giving this error?

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

CASE WHEN @.ProductNum >= 1 THEN @.Status = 'False' ELSE @.Status = 'True' END

RETURN @.Status

END

Code Snippet

Msg 156, Level 15, State 1, Procedure RestoranHazirMi, Line 25

Incorrect syntax near the keyword 'CASE'.

Msg 102, Level 15, State 1, Procedure RestoranHazirMi, Line 29

Incorrect syntax near 'END'.

CASE WHEN is a statement level construction, not flow control

You could use

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.Status = CASE WHEN COUNT(*)>=1 THEN 'False' ELSE 'True' END

FROM tblProduct

WHERE ProductID = @.ProductPrm

RETURN @.Status

END

OR (IF/ELSE for flow control):

Code Snippet

CREATE FUNCTION [dbo].[Ready]

(

-- Add the parameters for the function here

@.ProductPrm uniqueidentifier

)

RETURNS bit

AS

BEGIN

-- Declare the return variable here

DECLARE @.ProductNum int;

DECLARE @.Status bit;

-- Add the T-SQL statements to compute the return value here

SELECT @.ProductNum =COUNT(*)

FROM tblProduct

WHERE ProductID = @.ProductPrm

-- Return the result of the function

IF (@.ProductNum >= 1)

SET @.Status = 'False'

ELSE

SET @.Status = 'True'

RETURN @.Status

END

|||thanks dude. I've learned it now.sql

How to get the day and day number for a specified month

Hi Guys,

I'm trying to set up a report where the user can select a month, and the report will list the days and day numbers for that month. For example, if the user chooses August then the report will show:-

Wednesday 1st

Thursday 2nd

Friday 3rd

... etc

Can anybody help?

TIA.

Create a calendar table with the necessary data (as many years as you want. it will not be a big table even if you use the entire smalldatetime or datetime date range). You can then write a simple query that retrieves the days based on the month. You can also store other attributes like holidays, different calendars (fiscal, yearly, iso), weekends etc.

|||

If you let them choose the month and year, you can build the start date variable and use this script.

Code Snippet

DECLARE @.dateStart datetime

SET @.dateStart = '08/01/2007'

WHILE MONTH(@.dateStart) = 8

BEGIN

print(DATENAME(dw, @.dateStart) + ' ' + CAST(DAY(@.dateStart) AS VARCHAR(2)))

SET @.dateStart = @.dateStart + 1

END

This gives the output

Code Snippet

Wednesday 1

Thursday 2

Friday 3

Saturday 4

Sunday 5

Monday 6

Tuesday 7

Wednesday 8

Thursday 9

Friday 10

Saturday 11

Sunday 12

Monday 13

Tuesday 14

Wednesday 15

Thursday 16

Friday 17

Saturday 18

Sunday 19

Monday 20

Tuesday 21

Wednesday 22

Thursday 23

Friday 24

Saturday 25

Sunday 26

Monday 27

Tuesday 28

Wednesday 29

Thursday 30

Friday 31

sql

How to get Row Numbers from SQL Express 2005 query?

Hi,

I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error:

"The OVER SQL construct or statement is not supported."

So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)?

My current command is as follows:

SELECT (SELECT Row_Number()OVER (ORDER BY UserName)As RowNumber), *
FROM Users
ORDER BY UserName

Thanks for any advice you can offer!

like this

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber
FROM Customers )
select *
from Cust
Where RowNumber Between 20 and 30

Hope this helps

|||

SELECT ROW_NUMBER() OVER (ORDER BY ProductID) as RowNumber,ProductID, ProductName, UnitPrice FROM Products WHERE RowNumber BETWEEN 20 AND 30;

|||

Thank you both for your replies. I'm sorry, I think I my question may have been a bit misleading. I know that you can return a limited result set by using the statements you have provided, but the error message I am getting is as follows:

"The OVER SQL construct or statement is not supported."

Both your suggestions use the OVER construct, and this does not appear to be supported bySQL Server Express 2005. If I run your suggested queries inVisual Studio 2005 I get the aforementioned error and the query will not run (though the SQL validation check says it's fine).

So, is the "Row_Number() OVER" command supported by SQL Express 2005 or not? If not, what other ways can I return a limited result set?

|||

i think you are using the designer, go in the sql server management studio and to this

Select File / New / Query and type your queries in the editing window instead.

Hope this helps

|||

Run this in SQL SERVER 2005 Management Studio Express:

EXEC sp_dbcmptlevel yourDataBase

If the current compatibility level of your database is 80, then run this:

EXEC sp_dbcmptlevel yourDataBase, 90

You need the compatibility level at 90 to run the Row_Number() OVER() query and other new features.

|||

Hi Limno,

That sounds promising. I've tried getting my website database into SQL Server Management Studio Express in the past but without success. My database is held as a .MDF file within my project - is there a way of importing this directly into Management Studio? The "Connect To Server" dialog displayed on startup does not allow me to browse to a specific .MDF file, and the File -> Open dialog has no option to open .MDF files.

Thanks

|||

I just found out how to import an MDF here:

http://forums.asp.net/p/1147899/1871779.aspx#1871779

And my database shows a compatibility rating of 90. Using the Row_Number() command works within SQL Server Management Express Studio! In that case, how can I perform a query in Visual Studio 2005 that uses this command if Visual Studio does not support this command?

I'm using strongly typed Table Adapters and Data Tables using the DAL component in Visual Studio. Is it impossible? If not, can I at least perform this query programmatically (C#) and cast the results to my strongly typed Data Table? If it can be done programmatically, does anyone have any examples of how to do this?

Thanks again!

|||

In Visual Studio 2005, you will see that not support message. Have you tried to ingnore it and see what will happen? It seems is should work fine. Another way, you can wrap your query in a Stored Procedure to work with.