Search code examples
sql-serverstored-proceduresview

How can I use a stored procedure returned value in a function?

I have a table with a column for JSON. I created a stored procedure to combine each row's JSON column in a varchar(max). Now I am trying to get a subsection of said varchar(max) for a view. I'm having issues with the SUBSTRING function.

My attempt is as follows:

SUBSTRING(STORED_PROCEDURE, STRING_START, LEN(STORED_PROCEDURE) - STRING_END)

I am fairly new to SQL and haven't found any help or resolution. How can I use the results of the stored procedure without declaring the results before executing as I am doing this within a view?

Solution

  • You should use a function with a string return for JSON instead of a stored procedure.