Declaring Variables in View?

[RichardParry]

Hi All,

Quick thank you for the excellent support I have been receiving on here the last few days.

I have another question though, I have developed a pretty monsterous T-SQL query in View mode, however I need to declare some variables based on SELECT (integers, so I can use their values in a couple of places throughout the query), however I have tried to declare the variable as per usual (if I were doing it in a Store Proc), but it doesn't like that in a View, so I transplated the contents into a SP, but as soon as I declare a variable it says no data was returned when I run thwe query (it returns data per normal if I remove the variable declerations).

Any ideas how I can go about declaring a variable, either in a View or a Stored Proc, but also getting the data results to the screen as per a View mode?


Thanx!!! Richard

 

[SQLDenis]

can you show the code you have so far?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[RichardParry]

Update, to confirm my first variable decleration is as follows;

DECLARE @membertitlelength int
SET @membertitlelength = (SELECT MAX(LEN(TITLE)) AS TitleMaxLength FROM dbo.S_CLIENT HAVING (MAX(LEN(TITLE)) >= 1))

I have noted that, within the SP, if I remove the (SELECT...) and replace with, for example a single integer it will allow the SP to return results to screen, but won't return results when I try to set the variable to the SELECT record. I normally haven't had problems with this in the past, but would usually only use a SP through VB to interface with an application etc.

 

[SQLDenis]

why do you need the where clause max will always be max?


does this work?

DECLARE @membertitlelength int
SELECT @membertitlelength =  MAX(LEN(TITLE)) FROM  dbo.S_CLIENT

SELECT @membertitlelength

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[RichardParry]

Just tried that in a new SP and does the same, returns the following "The stored procedure executed successfully but did not return records

 

[SQLDenis]

can you post the complete proc code here

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Try adding SET NOCOUNT ON at the beginning of the stored procedure.

Ex:

Create proc blah
  @Param1 Int,
  @Param2 varchar(20)
AS
[!]SET NOCOUNT ON[/!]

[green]-- The rest of your code here[/green]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom