Really basic function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Really basic function

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2011-12-05 : 15:39:33

When we write stored procs we have to have a header. I always pull up an old SP and just copy and paste but I thought I'd make a function to print the header for me. So I did. But when I run the function it just tells me "Commands completed successfully". I'm not getting my text back. What do I need to change?


USE [mydb]
GO
/****** Object:  UserDefinedFunction [dbo].[startusp]    Script Date: 12/05/2011 13:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[startusp]() 
RETURNS VARCHAR(5000) AS

BEGIN
DECLARE @sql VARCHAR(5000)
SET @sql='
USE [dbname]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ---------------------------------------------------------------------
-- Stored Procedure: 
-- Creation Date:
-- Written by: Bob Smith
--
-- Purpose: 
--  
-- Input Parameters:
--  
-- Output Parameters: None  
--  
-- Return Status: None  
--  
-- Usage: Standard  
--  
-- Local Variables: 
--  
-- Called By: 
--  
-- Calls: None  
--  
-- Data Modifications:  
--  
-- Updates:
-- Date         Author  Review  Purpose  
-- ---------------------------------------------------------------------
';

return (@sql)
END
--exec startusp

Craig Greenwood

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-05 : 15:59:48

Use SELECT not EXEC: SELECT dbo.StartUsp()

craigwg
Posting Yak Master

154 Posts

Posted - 2011-12-05 : 16:03:08

Beauty. Print works even better!
Thanks

Craig Greenwood

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources